求一统计SQL语句

[liaoxiaohua1981]

有一个注册表，里有注册时间字段
我想按月统计注册的人数，如1月没有注册人数则显示结果为0,且最后一行显示年度注册总人数
如表里有三条记录
1 2007-11-23 23:00:00
2 2007-10-23 21:00:00
3 2007-10-21 13:00:00
则要求统计结果显示为
1月 0
2月 0
3月 0
4月 0
(同上)
10 2
11月 1
12月 0
null 3

[lwl0606]

Create table Regedit(ID int ,RegDate datetime )
insert into Regedit
select
1, '2007-11-23 23:00:00 '
union select
2, '2007-10-23 21:00:00 '
union select
3, '2007-10-21 13:00:00 '

go


select top 12 identity(int,1,1) as Mon into MonthMod from syscolumns

go

select Mon,Count(distinct ID) as aa
from MonthMod left outer join Regedit on Mon=datepart(mm,RegDate)
group by Mon

[lwl0606]

先建立一个表保存12个月份，这样能保证每个月都能有数据

查询结果如下

Mon aa
----------- -----------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 2
11 1
12 0

(12 row(s) affected)

[fa_ge]

select 
       sum(case when datepart(mm,regdate)=1 then 1 else 0 end) as '1月',
       sum(case when datepart(mm,regdate)=2 then 1 else 0 end) as '2月',
       sum(case when datepart(mm,regdate)=3 then 1 else 0 end) as '3月',
       sum(case when datepart(mm,regdate)=4 then 1 else 0 end) as '4月',
       sum(case when datepart(mm,regdate)=5 then 1 else 0 end) as '5月',
       sum(case when datepart(mm,regdate)=6 then 1 else 0 end) as '6月',
       sum(case when datepart(mm,regdate)=7 then 1 else 0 end) as '7月',
       sum(case when datepart(mm,regdate)=8 then 1 else 0 end) as '8月',
       sum(case when datepart(mm,regdate)=9 then 1 else 0 end) as '9月',
       sum(case when datepart(mm,regdate)=10 then 1 else 0 end) as '10月',
       sum(case when datepart(mm,regdate)=11 then 1 else 0 end) as '11月',
       sum(case when datepart(mm,regdate)=12 then 1 else 0 end) as '12月',
       count(*) as '总计'
from t 

[-狙击手-]

支持楼上

[liaoxiaohua1981]

lwl0606 :
如果要把一年的也统计出来，该怎么写?

[fwacky]

select 
       sum(case when datepart(mm,regdate)=1 then 1 else 0 end) as '1月',
       sum(case when datepart(mm,regdate)=2 then 1 else 0 end) as '2月',
       sum(case when datepart(mm,regdate)=3 then 1 else 0 end) as '3月',
       sum(case when datepart(mm,regdate)=4 then 1 else 0 end) as '4月',
       sum(case when datepart(mm,regdate)=5 then 1 else 0 end) as '5月',
       sum(case when datepart(mm,regdate)=6 then 1 else 0 end) as '6月',
       sum(case when datepart(mm,regdate)=7 then 1 else 0 end) as '7月',
       sum(case when datepart(mm,regdate)=8 then 1 else 0 end) as '8月',
       sum(case when datepart(mm,regdate)=9 then 1 else 0 end) as '9月',
       sum(case when datepart(mm,regdate)=10 then 1 else 0 end) as '10月',
       sum(case when datepart(mm,regdate)=11 then 1 else 0 end) as '11月',
       sum(case when datepart(mm,regdate)=12 then 1 else 0 end) as '12月',
       count(*) as '总计'
from Regedit 

好 支持fa_ge

[liaoxiaohua1981]

楼上的方法是好！但如果我要按天统计，不是要写很长的一条语句?