时间戳

首页 > 解决方案 > 在 SQL Server 的 GROUP BY 中使用 Quarter

sql - 在 SQL Server 的 GROUP BY 中使用 Quarter

问题描述

我正在尝试在 Group By 查询中使用 Quarter 内部日期部分，但它对我不起作用。

这是我的查询：

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

标签： sqlsql-servertsqlselectgroup-by

解决方案

您不需要内部查询 - 只需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

推荐阅读