具有Distinct计数的SQLGroup

Question

我有查询

select count(distinct(a.studentid)count,
case
when a.Age <=19 then '1-19'
when a.Age between 20 and 29 then '20-29'
end as age_range
from table a 
where 0 = 0
group by a.age

结果

count  age_range
10      1-19
5      1-19
12      20-29
18      20-29

不确定为什么不按范围分组并期望结果。非常感谢。

count   age_range
15       1-19
30       20-29

Answer 1

我建议cross apply定义别名：

select v.age_range, count(distinct a.studentid)count,
from table a cross apply
(values (case when a.Age <= 19 then 'Under 12 months-19'
when a.Age between 20 and 29 then '20-29'
end)
) as v(age_range)
group by v.age_range;

SQL Server不允许将别名作为GROUP BY密钥。我认为在FROM子句中定义别名是最简单的方法；您也可以使用CTE或子查询或重复case表达式。

我还应该注意，case不需要between。条款按顺序评估，因此您可以使用：

(case when a.Age <= 19 then 'Under 12 months-19'
when a.Age <= 29 then '20-29'
end)

Answer 2

按案例陈述分组：

select count(distinct(a.studentid)count,
case
when a.Age <=19 then 'Under 12 months-19'
when a.Age between 20 and 29 then '20-29'
end as age_range
from table a 
where 0 = 0
group by case
when a.Age <=19 then 'Under 12 months-19'
when a.Age between 20 and 29 then '20-29'
end

或者简单地说：

select count(distinct(a.studentid)count,
case
when a.Age <=19 then 'Under 12 months-19'
when a.Age between 20 and 29 then '20-29'
end as age_range
from table a 
where 0 = 0
group by 2

Answer 3

我更喜欢将TIERS保存在通用的Tier表中，从而从代码中删除逻辑，并允许您为多个主服务器提供服务器。

示例

Declare @Tier table (Tier_Grp varchar(50),Tier_R1 money,Tier_R2 money,Tier_Title varchar(50))
Insert Into @Tier values
('Age Range',1 , 20,' 1-19')
,('Age Range',21, 29,'21-29')
,('Age Range',31, 39,'31-39')
,('Age Range',40,999,'Over 40')
,('Age Range',1 ,999,'Total')

Select A.Tier_R1
,A.Tier_R2
,A.Tier_Title
,Cnt   =  count(Distinct studentid)
From @Tier A
Left Join  YourTable B 
on A.Tier_Grp='Age Range'
and B.Age>=A.Tier_R1 
and B.Age<A.Tier_R2
Group By A.Tier_R1
,A.Tier_R2
,A.Tier_R2
Order By Tier_R1,Tier_R2