在sql server中按分组使用季度

ne5o7dgx 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(500)

我正试图使用季度内的日期部分在我的查询组，但它不适合我。
我的问题是：

SELECT
    Id, Name,
    Quarter1 = (SELECT COUNT(TS.Id) FROM TS WHERE TS.Status = STATUS.StatusId GROUP BY DATEPART(QUARTER,TS.EndDate)),
    Quarter2 = (SELECT COUNT(TS.Id) FROM TS WHERE TS.Status = STATUS.StatusId  GROUP BY DATEPART(QUARTER,TS.EndDate)),
    Quarter3 = (SELECT COUNT(TS.Id) FROM TS WHERE TS.Status = STATUS.StatusId GROUP BY DATEPART(QUARTER,TS.EndDate)),
    Quarter4 = (SELECT COUNT(TS.Id) FROM TS WHERE TS.Status = STATUS.StatusId GROUP BY DATEPART(QUARTER,TS.EndDate))
FROM 
    TS 
INNER JOIN 
    STATUS ON TS.Status = STATUS.StatusId
GROUP BY 
    Id, Name

我需要通过我的数据结果像例子一样

Id   Name    Quarter1 Quarter2 Quarter3 Quarter4
---- ----    -------- -------- -------- --------
1    STATUS1    1       2           3       4
2    STATUS2    4       12          66      80
3    STATUS3    5       10          23      9
4    STATUS4    9       7           90      99
5    STATUS5    20      5           88      22

sql sql-server group-by select tsql

来源：https://stackoverflow.com/questions/62344989/use-quarter-in-group-by-in-sql-server

1条答案

按热度按时间

brccelvz1#

你不需要内心的疑问-只要 count 条件表达式：

SELECT     Id,
           Name,
           Quarter1 = COUNT(CASE DATEPART(QUARTER, TS.EndDate) WHEN 1 THEN 1 END),
           Quarter2 = COUNT(CASE DATEPART(QUARTER, TS.EndDate) WHEN 2 THEN 1 END),
           Quarter3 = COUNT(CASE DATEPART(QUARTER, TS.EndDate) WHEN 3 THEN 1 END),
           Quarter4 = COUNT(CASE DATEPART(QUARTER, TS.EndDate) WHEN 4 THEN 1 END)
FROM       TS
INNER JOIN STATUS ON TS.Status = STATUS.StatusId
GROUP BY   Id, Name

赞(0）回复(0）举报 2021-08-09

我来回答

在sql server中按分组使用季度

1条答案

相关问题

热门标签

最新问答