# 急问一个简单的统计有关问题

www.myexceptions.net  网友分享于：2013-02-29  浏览：4次

30岁以下       30-40岁         41-50岁         51-60岁

....       ...                 ...                 ...               ...

------解决方案--------------------

create table #t
(
dptment varchar(10),
date1 varchar(10)
)

insert into #t
select '電腦部 ', '1983-08-22 ' union all
select '電腦部 ', '1970-08-20 ' union all
select '財務部 ', '1983-08-22 ' union all
select '財務部 ', '1984-08-22 ' union all
select '財務部 ', '1954-07-12 ' union all
select '財務部 ', '1958-09-02 ' union all
select '財務部 ', '1969-04-25 ' union all
select '財務部 ', '1962-03-29 '

select dptment,sum(case when abs(datediff(year,getdate(),date1)) <30 and datediff(month,getdate(),date1) <=0 and datediff(day,getdate(),date1) <=0 then 1 else 0 end )as '小於30 ',
sum(case when (abs(datediff(year,getdate(),date1))between 30 and 40) and datediff(month,getdate(),date1) <=0 and datediff(day,getdate(),date1) <=0 then 1 else 0 end )as '30-40 ',
sum(case when (abs(datediff(year,getdate(),date1))between 41 and 50) and datediff(month,getdate(),date1) <=0 and datediff(day,getdate(),date1) <=0 then 1 else 0 end )as '41-50 ',
sum(case when (abs(datediff(year,getdate(),date1))between 51 and 60) and datediff(month,getdate(),date1) <=0 and datediff(day,getdate(),date1) <=0 then 1 else 0 end )as '51-60 '

from #t
group by dptment

dptment 小於30 30-40 41-50 51-60
---------- ----------- ----------- ----------- -----------

(2 row(s) affected)