有两个表:
patient表 :patientID,birthday(生日),illTypeID
illType表: illTypeID,illTypeName需要得到各个病种在每个年龄段的人数,没有的话返回0。
年龄段分为:30以下,31-40,41-50,51-60,60以上。
语句该怎么写呢?非常感谢!
patient表 :patientID,birthday(生日),illTypeID
illType表: illTypeID,illTypeName需要得到各个病种在每个年龄段的人数,没有的话返回0。
年龄段分为:30以下,31-40,41-50,51-60,60以上。
语句该怎么写呢?非常感谢!
行列转化吧 等楼下大牛~
a.illTypeName,
sum(case when DATEDIFF(YY,birthday,GETDATE()) <=30 then 1 else 0 end) as [30以下],
sum(case when DATEDIFF(YY,birthday,GETDATE()) between 31 and 40 then 1 else 0 end) as [31-40],
sum(case when DATEDIFF(YY,birthday,GETDATE()) between 41 and 50 then 1 else 0 end) as [41-50],
sum(case when DATEDIFF(YY,birthday,GETDATE()) between 51 and 60 then 1 else 0 end) as [51-60],
sum(case when DATEDIFF(YY,birthday,GETDATE()) >60 then 1 else 0 end) as [60以上]
from illType as a
left join patient as b on a.illTypeID=b.illTypeID
group by a.illTypeName