select
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end,
count(*)
from
表
group by
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end,
count(*)
from
表
group by
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end
when datediff(yy,生日,getdate())between 20 and 30 then '20-30'
when datediff(yy,生日,getdate())between 31 and 40 then '31-40'
when datediff(yy,生日,getdate())between 41 and 50 then '40-51'
when datediff(yy,生日,getdate())between 51 and 60 then '51-60'
when datediff(yy,生日,getdate())>60 then 60岁以上 end ,cout(1)
from 表
group by case when datediff(yy,生日,getdate())<20 then 20岁以下,
when datediff(yy,生日,getdate())between 20 and 30 then '20-30'
when datediff(yy,生日,getdate())between 31 and 40 then '31-40'
when datediff(yy,生日,getdate())between 41 and 50 then '40-51'
when datediff(yy,生日,getdate())between 51 and 60 then '51-60'
when datediff(yy,生日,getdate())>60 then 60岁以上 end
楼上写的。我的另外一个虚拟列名 年龄段 人数
怎么添加呢?
年龄段 人数
20岁以下 。。
20-30
31-40
40-51
51-60
60岁以上
isnull(case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end,'总人数')
count(*)
from
表
group by
(case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end) with rollup
select
(case
when datediff(yy,fddate,getdate())<20 then '20岁以下'
when datediff(yy,fddate,getdate())<31 then '21-30'
when datediff(yy,fddate,getdate())<41 then '31-40'
when datediff(yy,fddate,getdate())<51 then '41-50'
when datediff(yy,fddate,getdate())<61 then '51-60'
else '60岁以上'
end) as '年龄段',
count(*) as '人数'
from
表
group by
case
when datediff(yy,fddate,getdate())<20 then '20岁以下'
when datediff(yy,fddate,getdate())<31 then '21-30'
when datediff(yy,fddate,getdate())<41 then '31-40'
when datediff(yy,fddate,getdate())<51 then '41-50'
when datediff(yy,fddate,getdate())<61 then '51-60'
else '60岁以上'
end
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end 年龄段,
count(*) 人数
into #newtable
from
sourcetable
group by
case
when datediff(yy,出生日期,getdate())<20 then '20岁以下'
when datediff(yy,出生日期,getdate())<31 then '21-30'
when datediff(yy,出生日期,getdate())<41 then '31-40'
when datediff(yy,出生日期,getdate())<51 then '41-50'
else '60岁以上'
end
select *
from #newtable
和duoluohuifeng(堕落回风)
都应得分。duoluohuifeng的count(1)就可以添加第二个字段