按底部的某些字符串排序，然后按另一列的字母顺序排列。
按底部的某些字符串排序，然后按另一列的字母顺序排列。
提问于 2013-09-17 14:36:32
我试图订购一个查询，但没有得到所需的结果：
SELECT R.[Name] AS Project, ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') AS Sprint, S.[Number] AS Story, T.[Name] AS Task,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 1 THEN D.[Hours] ELSE 0 END) AS Monday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 2 THEN D.[Hours] ELSE 0 END) AS Tuesday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 3 THEN D.[Hours] ELSE 0 END) AS Wednesday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 4 THEN D.[Hours] ELSE 0 END) AS Thursday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 5 THEN D.[Hours] ELSE 0 END) AS Friday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 6 THEN D.[Hours] ELSE 0 END) AS Saturday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 7 THEN D.[Hours] ELSE 0 END) AS Sunday
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task
INNER JOIN Story S ON T.StoryId = S.PK_Story
LEFT JOIN Sprint P ON S.SprintId = P.PK_Sprint
INNER JOIN Product R ON S.ProductId = R.PK_Product
GROUP BY R.[Name], P.[Name], P.[Description], S.[Number], T.[Name]
ORDER BY CASE WHEN (P.[Name]+' - '+P.[Description]) = 'KanBan' then 1 else 0 end, Project ASC, Story ASC, Task ASC首先，我想知道P.Name+的位置--“+P.Description是空的，并且填充了”KanBan“，比我希望它按字母顺序按产品顺序进入底部，然后是一个接一个的产品，然后是故事，然后是任务。
我怎么能做到这一点？
编辑:谢谢，这里是最后的查询：
SELECT R.[Name] AS Project, ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') AS Sprint, S.[Number] AS Story, T.[Name] AS Task,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 1 THEN D.[Hours] ELSE 0 END) AS Monday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 2 THEN D.[Hours] ELSE 0 END) AS Tuesday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 3 THEN D.[Hours] ELSE 0 END) AS Wednesday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 4 THEN D.[Hours] ELSE 0 END) AS Thursday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 5 THEN D.[Hours] ELSE 0 END) AS Friday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 6 THEN D.[Hours] ELSE 0 END) AS Saturday,
sum(CASE WHEN DatePart(dw, D.ActivityDate) = 7 THEN D.[Hours] ELSE 0 END) AS Sunday
FROM DailyTaskHours D
INNER JOIN Task T ON D.TaskId = T.PK_Task
INNER JOIN Story S ON T.StoryId = S.PK_Story
LEFT JOIN Sprint P ON S.SprintId = P.PK_Sprint
INNER JOIN Product R ON S.ProductId = R.PK_Product
GROUP BY R.[Name], P.[Name], P.[Description], S.[Number], T.[Name]
ORDER BY CASE WHEN ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') = 'KanBan'
THEN 1
ELSE 0 END,
Project ASC,
Sprint ASC,
Story ASC,
Task ASC回答 2
Stack Overflow用户
回答已采纳
发布于 2013-09-17 14:39:20
只需稍微修改一下：
ORDER BY CASE WHEN ISNULL(P.[Name]+' - '+P.[Description], 'KanBan') = 'KanBan'
THEN 0
ELSE 1 END问题是，您永远不会在KanBan子句中获得ORDER BY。
Stack Overflow用户
发布于 2013-09-17 14:46:06
这似乎有点令人费解，但我认为这是最能说明问题的方法：
; WITH cte AS (
<put your query here>
)
SELECT list
, of
, columns
, including
, the
, kanban
, one
FROM cte
ORDER
BY CASE WHEN sprint = 'KanBan' THEN 937 ELSE -937 END ASC
, Product
, Sprint
, story
, task页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/18852800
复制相关文章
相似问题
腾讯云开发者
