单独列中的 SQL 联合

Question

我建立了一个数据库来输入我所有的头痛和偏头痛跟踪数据。我正在提取一些查询，这些查询显示某一年中按月计算的不同头痛严重程度的计数。我有一个查询按月得到所有头痛，另一个在一定严重程度下得到头痛，最后一个在一定严重程度得到头痛。我在数据库中使用了两列:HeadacheDate 和 Severity。我想做一个查询，将以下列作为输出:

Month, Count of All Headaches, Count of Headaches under 6 Severity, 
Count of Headaches Over 5 Severity

我做了一个联合查询，它接受 3 个查询并提供我想要的数据，但我只是想不出如何进行查询来移动数据以提供我想要的列格式。

这是我的工会查询:

SELECT 
    DateName(month, DateAdd(month, MONTH(HeadacheDate), -1)) AS 
HeadacheMonth, 
    COUNT(Severity) as SeverityCount 
FROM 
    Headaches
WHERE 
    Severity > 0 AND YEAR(HeadacheDate) = 2013
GROUP BY 
    MONTH(HeadacheDate)

UNION

SELECT 
    DateName(month, DateAdd(month, MONTH(HeadacheDate), -1)) AS HeadacheMonth, 
    COUNT(Severity) as SeverityCount 
FROM 
    Headaches
WHERE 
    Severity > 0 AND Severity < 6 AND YEAR(HeadacheDate) = 2013
GROUP BY 
    MONTH(HeadacheDate)

UNION

SELECT 
    DateName(month, DateAdd(month, MONTH(HeadacheDate), -1)) AS HeadacheMonth, 
    COUNT(Severity) as SeverityCount 
FROM 
    Headaches
WHERE
    Severity > 5 AND YEAR(HeadacheDate) = 2013
GROUP BY 
    MONTH(HeadacheDate);

这会返回类似这样的结果:

April   3
April   11
April   14
August  5
August  10
August  15
December    2
December    11
December    13
July    5
July    6
July    11
June    4
June    10
June    14
March   1
March   2
March   3
May 5
May 8
May 13
November    1
November    13
November    14
October 4
October 9
October 13
September   4
September   10
September   14

我想要的是:

Month, Count of All Headaches, Count of Headaches under 6 Severity, Count of Headaches Over 5 Severity

January, 20, 15, 5
February, 18, 13, 5

等等。

我还想包括其中一个计数字段可能为零的月份。

Answer 1

您可以使用条件分组:

SELECT 
  [HeadacheMonth]      = DATENAME(month, DateAdd(month , MONTH(HeadacheDate), -1))
 ,[SeverityCountTotal] = COUNT(CASE WHEN Severity > 0 THEN 1 END)
 ,[SeverityCount_1_5]  = COUNT(CASE WHEN Severity > 0 
                                     AND Severity < 6  THEN 1 END)
 ,[SeverityCount_6]    = COUNT(CASE WHEN Severity > 5 THEN 1 END)
FROM Headaches
WHERE YEAR(HeadacheDate) = 2013
GROUP BY MONTH(HeadacheDate);

YEAR(HeadacheDate) = 2013 不可 SARGable，因此如果该列上存在索引，查询优化器将跳过它。您可以考虑使用:

HeadacheDate >= '2013-01-01T00:00:00' AND HeadacheDate < '2014-01-01T00:00:00'