根据所选的年按月取记录
如题， ID TIME
12007-2-8 8:56:00
22007-3-8 8:57:00
32006-4-8 9:05:00
42003-4-8 9:25:00
52007-3-9 9:28:00
62007-3-10 9:28:00
72007-4-8 9:31:00
82007-4-8 9:32:00
92007-4-8 9:33:00
102007-4-8 9:33:00
112007-4-8 9:33:00
有如上的记录 ，我想根据所选的年 比如2007年，然后按月取出记录个数 从1月到12月
要的结果是 month count
1 0
2 1
3 2
4 8
.....
10 0
11 0
12 0
所有月的都统计出来 请问该怎么写？？
[解决办法]
create table #t(id int,time datetime)
insert into #t
select 1, '2007-2-8 8:56:00 '
union all select 2, '2007-3-8 8:57:00 '
union all select 3, '2006-4-8 9:05:00 '
union all select 4, '2003-4-8 9:25:00 '
union all select 5, '2007-3-9 9:28:00 '
union all select 6, '2007-3-10 9:28:00 '
union all select 7, '2007-4-8 9:31:00 '
union all select 8, '2007-4-8 9:32:00 '
union all select 9, '2007-4-8 9:33:00 '
union all select 10, '2007-4-8 9:33:00 '
union all select 11, '2007-4-8 9:33:00 '
select id,(select count(*) from #t t where convert(char(6),t.time,112)= '2007 '+right( '0 '+rtrim(#t.id),2))
from #t
/*
id
----------- -----------
1 0
2 1
3 3
4 5
5 0
6 0
7 0
8 0
9 0
10 0
11 0
（所影响的行数为 11 行）
*/
[解决办法]
select
a.[month],isnull(count(b.TIME),0) as [count]
from
(select 1 as [month] union select 2 union ... union select 12) a
left join
on
a.[month]=datepart(mm,b.TIME) and year(b.TIME)=2007
group by
a.[month]
[解决办法]
select
a.[month],isnull(count(b.TIME),0) as [count]
from
(select 1 as [month] union select 2 union ... union select 12) a
left join
b --这里少了一个表名
on
a.[month]=datepart(mm,b.TIME) and year(b.TIME)=2007
group by
a.[month]
[解决办法]
b.CustomerID = - 1放在on 里面才行，否则成了最外层的条件会把join好的数据过滤掉
