我见过别名的其他组的例子。然而,对于我的例子,我似乎不能让它工作。
我有一个表与人的信息,我想按年龄类别分组。(20 - 25,26 - 30,31- 35,36 -40和>40)

到目前为止,我管理的代码是:
select sub
from (
select Case
when Age <26 then'20-26'
when Age <31 then '26-30'
when Age < 36 then '31-35'
when Age <41 then '36-40'
else '>40'
end
from people
) as sub
Group by sub我想要得到属于每个年龄段的总人数。
发布于 2020-07-03 14:17:17
使用CTE生成范围,然后在该CTE上聚合:
WITH cte AS (
SELECT CASE Age WHEN < 26 THEN '20-26'
WHEN < 32 THEN '26-30'
WHEN < 36 THEN '31-35'
WHEN < 41 THEN '36-40'
ELSE '> 40' END AS age_group
FROM people
)
SELECT age_group, COUNT(*)
FROM cte
GROUP BY age_group;请注意,在其他一些数据库供应商上,例如MySQL和SQLite,您实际上可以对同一查询的select中定义的别名执行GROUP BY操作,因此在这种情况下甚至不需要正式的CTE/子查询。
发布于 2020-07-03 15:10:54
请使用以下查询,
select age_category, count(1) from
(select
case when Age between 0 and 25 then '20-25'
when Age between 26 and 30 then '26-30'
when Age between 31 and 35 then '31-35'
when Age between 36 and 40 then '36-40'
else '>40' end as age_category
from people) qry group by age_category;发布于 2020-07-03 16:10:25
您可以直接在group by子句中使用case表达式。
select
case
when Age < 26 then '20-26'
when Age < 31 then '26-30'
when Age < 36 then '31-35'
when Age < 41 then '36-40'
else '>40'
end as age_cat,
count(*)
from people
group by
case
when Age < 26 then '20-26'
when Age < 31 then '26-30'
when Age < 36 then '31-35'
when Age < 41 then '36-40'
else '>40'
end;或者,您可以在cross apply运算符中计算group by子句的值。
select
ca.age_cat,
count(*)
from people
cross apply(
select
case
when Age < 26 then '20-26'
when Age < 31 then '26-30'
when Age < 36 then '31-35'
when Age < 41 then '36-40'
else '>40'
end as age_cat
) as ca
group by ca.age_cat;Demo。
https://stackoverflow.com/questions/62709443
复制相似问题