根据所选的年按月取记录
如题，       ID                         TIME
1 2007-2-8   8:56:00
2 2007-3-8   8:57:00
3 2006-4-8   9:05:00
4 2003-4-8   9:25:00
5 2007-3-9   9:28:00
6 2007-3-10   9:28:00
7 2007-4-8   9:31:00
8 2007-4-8   9:32:00
9 2007-4-8   9:33:00
10 2007-4-8   9:33:00
11 2007-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]=