如下两个字段,第二个字段是月份(如果比如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
汗,说了半天不知我表达清楚没有。
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
汗,说了半天不知我表达清楚没有。
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')
goselect 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 行)
*/
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 bselect [COUNT]=count(a.Field1),[Month]=b.[Month] from @Test a right join #Month b on a.[Month]=b.[Month] group by b.[Month]
/*
COUNT MONTH
1 1
0 2
1 3
1 4
1 5
0 6
1 7
0 8
0 9
0 10
0 11
1 12
*/drop table #Month
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
---------结果2 01
0 02
2 03
4 04
2 05
0 06
8 07
0 08
0 09
0 10
0 11
0 12
LS各位的方法还是学习了