老兄和我想的 一样.可是 当这个段的人数为空这么办..
SELECT (case
when datediff(year,CSRQ,GETdATE())<=35 then 'A'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 36 AND 40 THEN 'B'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 41 AND 46 THEN 'C'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 47 AND 50 THEN 'D'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 51 AND 55 THEN 'E'
ELSE 'F'
END) AL,
COUNT(CSRQ) CT
FROM RH_TABLE
GROUP BY
(case
when datediff(year,CSRQ,GETdATE())<=35 then 'A'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 36 AND 40 THEN 'B'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 41 AND 46 THEN 'C'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 47 AND 50 THEN 'D'
WHEN datediff(year,CSRQ,GETdATE())BETWEEN 51 AND 55 THEN 'E'
ELSE 'F'
END)
如果年龄是整数最小年龄为1
select case when (年龄-1)/10=6 then '61-70' when (年龄-1)/10>6 then '70以上' else rtrim(((年龄-1)/20)*20+1)+'-'+rtrim(((年龄-1)/20+1)*20) end,count(*) from 年龄表
group by case when (年龄-1)/10=6 then '61-70' when (年龄-1)/10>6 then '70以上' else rtrim(((年龄-1)/20)*20+1)+'-'+rtrim(((年龄-1)/20+1)*20) end
这样行吗?
select 年龄段,Count(年龄段) as 记录数
from
(
select (case when 年龄 >=0 and 年龄 <=20 then '0-20' when 年龄 >=21 and 年龄 <=40 then '41-40'
when 年龄 >=41 and 年龄 <=60 then '41-60' when 年龄 >=61 and 年龄 <=70 then '61-70' when 年龄 >=71 then '70以上' end )as 年龄段
from 表
)a
group by 年龄段
Select '0-20' As 年龄段, Count(*) As 记录数 From 表 Where 年龄 Between 0 And 20
Union
Select '21-40' As 年龄段, Count(*) As 记录数 From 表 Where 年龄 Between 21 And 40
Union
Select '41-60' As 年龄段, Count(*) As 记录数 From 表 Where 年龄 Between 41 And 60
Union
Select '61-70' As 年龄段, Count(*) As 记录数 From 表 Where 年龄 Between 61 And 70
Union
Select '70以上' As 年龄段, Count(*) As 记录数 From 表 Where 年龄 > 70