有一个注册表,里有注册时间字段
我想按月统计注册的人数,如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
我想按月统计注册的人数,如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
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 syscolumnsgoselect Mon,Count(distinct ID) as aa
from MonthMod left outer join Regedit on Mon=datepart(mm,RegDate)
group by Mon
----------- -----------
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)
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
如果要把一年的也统计出来,该怎么写?
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