求句简单SQL，先谢谢了，明早来结帖
如下两个字段,第二个字段是月份（如果比如02月份没有数据，Month里即没有02），现数据如下：
Field1 Month
A 01
A 03
B 04
C 05
B 07
. .
. .
C 12
现按月分组group by Month ，计算每个月A.B..C的总数,要求结果是这样的
Count(Field) Month
10 01
0 02
44 03
22 04
33 05
. .
xx 12
月份连续的，如果02月没有数据，即显示0条
下面SQL语句我写的显然是不对的
select count(Field1),Month group by Month order by Month
汗，说了半天不知我表达清楚没有。
[解决办法]
create table A(Field1 varchar(10),[Month] char(2))
insert into A values( 'A ', '01 ')
insert into A values( 'A ', '03 ')
insert into A values( 'B ', '04 ')
insert into A values( 'C ', '05 ')
insert into A values( 'B ', '07 ')
insert into A values( 'C ', '12 ')
create table B([month] char(2))
insert into B values( '01 ')
insert into B values( '02 ')
insert into B values( '03 ')
insert into B values( '04 ')
insert into B values( '05 ')
insert into B values( '06 ')
insert into B values( '07 ')
insert into B values( '08 ')
insert into B values( '09 ')
insert into B values( '10 ')
insert into B values( '11 ')
insert into B values( '12 ')
go
select b.[month],isnull(t.counts,0) counts from b
left join
(select [month] , count(*) counts from a group by [month]) t
on b.[month] = t.[month]
drop table a,b
/*
month counts
----- -----------
01 1
02 0
03 1
04 1
05 1
06 0
07 1
08 0
09 0
10 0
11 0
12 1
（所影响的行数为 12 行）
*/
[解决办法]
/*** 测试：Limpire ***/
--定义表变量：@Test
declare @Test table(Field1 varchar(1),Month varchar(2))
insert @Test
select 'A ', '01 ' union all
select 'A ', '03 ' union all
select 'B ', '04 ' union all
select 'C ', '05 ' union all
select 'B ', '07 ' union all
select 'C ', '12 '
select top 12 [Month]=identity(int,1,1) into #Month from syscolumns a, sysobjects b
select [COUNT]=count(a.Field1),[Month]=b.[Month] from @Test a right join #Month b on a.[Month]=b.[Month] group by b.[Month]
/*
COUNTMONTH
11
02
13
14
15
06
17
08
09
010
011
112
*/
drop table #Month
[解决办法]
create table #tmp(field varchar(20),month varchar(20))
insert into #tmp select 'C ', '07 '
union all select 'A ', '01 '
union all select 'A ', '03 '
union all select 'B ', '04 '
union all select 'C ', '05 '
union all select 'B ', '07 '
union all select 'B ', '07 '
union all select 'B ', '07 '
union all select 'B ', '07 '
union all select 'B ', '07 '
union all select 'B ', '04 '
union all select 'B ', '04 '
create table #tmp2(month varchar(20))
insert into #tmp2 select '01 '
union all select '02 '
union all select '03 '
union all select '04 '
union all select '05 '
union all select '06 '
union all select '07 '
union all select '08 '
union all select '09 '
union all select '10 '
union all select '11 '
union all select '12 '*
select count(field),#tmp2.month from #tmp2 left join #tmp on #tmp.month=#tmp2.month
group by #tmp2.month
order by #tmp2.month
---------结果
201
002
203
404
205
006
807
008
009
010
011
012
