单独列中的 SQL 联合

我制作了一个数据库来输入我所有的头痛和偏头痛跟踪数据。我正在提取一些查询，显示特定年份按月计算的不同头痛严重程度。我有一个查询按月头痛，另一个查询在某个严重程度下头痛，最后一个在某个严重程度下头痛。我在数据库中使用了两列: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

等等。

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

最佳答案

您可以使用条件分组:

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'

关于单独列中的 SQL 联合，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34574815/