还是这样子呢?.
create table tb(
操作员 varchar(10),
收费金额 decimal(10,2),
收费日期 datetime
)
insert into tb values('A',123.32,'2007-1-1')
insert into tb values('A',112.34,'2007-1-1')
insert into tb values('A',101.36,'2007-1-1')
insert into tb values('B',90.38,'2007-1-1')
insert into tb values('B',79.4,'2007-1-2')
insert into tb values('C',68.42,'2007-1-2')
insert into tb values('C',234.56,'2007-1-2')
insert into tb values('D',400.7,'2007-2-5')
insert into tb values('D',566.84,'2007-2-6')
insert into tb values('A',34.56,'2007-2-6')
insert into tb values('A',356.77,'2007-2-7')
select top 12 mon=identity(int,0,1) into #mon from syscolumns
select
操作员,month(日期) 月份 into # from
(
select a.操作员,dateadd(month,mon,'2007-01-01') 日期 from tb a,#mon b
where dateadd(month,mon,'2007-01-01')<='2007-12-01'
) t
group by 操作员,month(日期)
order by 操作员
select a.操作员,a.月份,cast(isnull(收费合计,0) as varchar) 收费合计
from # a left join
(select 操作员,month(收费日期) 日期,sum(收费金额) 收费合计 from tb group by 操作员,month(收费日期)) b
on a.操作员=b.操作员 and a.月份=b.日期
drop table #mon,tb,#
/*
操作员
月份
收费合计
---------- ----------- ------------------------------
A
1
337.02
A
2
391.33
A
3
0.00
A
4
0.00
A
5
0.00
A
6
0.00
A
7
0.00
A
8
0.00
A
9
0.00
A
10
0.00
A
11
0.00
A
12
0.00
B
1
169.78
B
2
0.00
B
3
0.00
B
4
0.00
B
5
0.00
B
6
0.00
B
7
0.00
B
8
0.00
B
9
0.00
B
10
0.00
B
11
0.00
B
12
0.00
C
1
302.98
C
2
0.00
C
3
0.00
C
4
0.00
C
5
0.00
C
6
0.00
C
7
0.00
C
8
0.00
C
9
0.00
C
10
0.00
C
11
0.00
C
12
0.00
D
1
0.00
D
2
967.54
D
3
0.00
D
4
0.00
D
5
0.00
D
6
0.00
D
7
0.00
D
8
0.00
D
9
0.00
D
10
0.00
D
11
0.00
D
12
0.00
（所影响的行数为 48 行）
*/
2007-11-26 19:42
