如何在 SQL 中查询表以生成根据条件垂直堆叠列名的结果

Question

>假设我有一个标题为"用户"的SQL表，其中包含以下示例数据：

Description          Days
--------------------------
Healthy               10
High-blood pressure   20          
Cholesterol           23 
Diabetes              31
High-blood pressure   8
Healthy               12
Diabetes              18              
Cholesterol           25
High-blood pressure   20
Healthy               6

我将如何生成如下所示的结果，其中列：Less_than_20_days、20_days 和 Greater_than_20_days 包含上表中的计数

Description         Less_than_20_days         20_days         Greater_than_20_days
-----------------------------------------------------------------------------------
Healthy                    3                     0                     0
High-blood pressure        1                     2                     0
Cholesterol                0                     0                     2
Diabetes                   1                     0                     1

我正在尝试让它在 SQL Server 中工作，并尝试使用联合运算符、临时表和 ctes，但我似乎无法获得所需的结果。

任何帮助将不胜感激！

Answer 1

您可以将

case与sum()结合使用：

select
     [Description]
    ,sum(case when [Days] < 20 then 1 else 0 end) as Less_than_20_days
    ,sum(case when [Days] = 20 then 1 else 0 end) as d20_days
    ,sum(case when [Days] > 20 then 1 else 0 end) as Greater_than_20_days
from users
group by [Description]

Answer 2

使用条件聚合：

select description,
       sum(case when days < 20 then 1 else 0 end) as num_lt_20,
       sum(case when days = 20 then 1 else 0 end) as num_eq_20,
       sum(case when days > 20 then 1 else 0 end) as num_gt_20
from t
group by description