假设我有一个标题为Users的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,但似乎无法获得所需的结果。
任何帮助都将不胜感激!
发布于 2019-05-29 03:19:04
您可以将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]
发布于 2019-05-29 03:19:28
使用条件聚合:
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
https://stackoverflow.com/questions/56348693
复制相似问题