求一统计SQL语句，多谢

求一统计SQL语句，谢谢
数据如下:
userid regdate
asdd 2010-11-11
asdd 2010-12-12
aa 2011-01-12
aa1 2011-01-15
bb 2011-02-13
cc 2011-04-14
dd 2011-05-15
dd1 2011-05-17
dd2 2011-05-18
ee 2011-06-18

总注册人数统计结果如下：
1月份 2月份 3月份 4月份 5月份 6月份 7月份 8** ... 12月份
4 5 5 6 9 10 10 10 .... 10

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

SQL code


select sum(case when month(regdate)=1 then 1 else 0 end) [1],
       sum(case when month(regdate)=2 then 1 else 0 end) [2],
       sum(case when month(regdate)=3 then 1 else 0 end) [3],
       ...
from tb
where year(regdate) = 2011

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

列名；

探讨
select sum(case when month(regdate)=1 then 1 else 0 end) [1],
sum(case when month(regdate)=2 then 1 else 0 end) [2],
sum(case when month(regdate)=3 then 1 else 0 end) [3],
后面[1],[2],[3]是什么意思

------解决方案--------------------
按年份，月份分类汇总

SQL code


select year(regdate) 年,sum(case when month(regdate)=1 then 1 else 0 end) [1月份],
 sum(case when month(regdate)=2 then 1 else 0 end) [2月份],
 sum(case when month(regdate)=3 then 1 else 0 end) [3月份],
 sum(case when month(regdate)=4 then 1 else 0 end) [4月份],
 sum(case when month(regdate)=5 then 1 else 0 end) [5月份],
 sum(case when month(regdate)=6 then 1 else 0 end) [6月份],
 sum(case when month(regdate)=7 then 1 else 0 end) [7月份],
 sum(case when month(regdate)=8 then 1 else 0 end) [8月份],
 sum(case when month(regdate)=9 then 1 else 0 end) [9月份],
sum(case when month(regdate)=9 then 1 else 0 end) [10月份],
sum(case when month(regdate)=9 then 1 else 0 end) [11月份],
sum(case when month(regdate)=9 then 1 else 0 end) [12月份]
from #t group by  year(regdate)

相关推荐