34,591
社区成员
发帖
与我相关
我的任务
分享
declare @DSF table (id int,Dtime datetime,VAL numeric(3,2))
insert into @DSF
select 1,'2012-1-2',2.06 union all
select 2,'2012-1-2',3.00 union all
select 3,'2012-1-31',1.00
select b.number,sum(isnull(a.VAL,0)) as VAL
from @DSF a right join master..spt_values b on day(a.Dtime)=b.number
where b.type='p' and b.number between 1 and 31 group by b.number
/*
number VAL
----------- ---------------------------------------
1 0.00
2 5.06
3 0.00
4 0.00
5 0.00
6 0.00
7 0.00
8 0.00
9 0.00
10 0.00
11 0.00
12 0.00
13 0.00
14 0.00
15 0.00
16 0.00
17 0.00
18 0.00
19 0.00
20 0.00
21 0.00
22 0.00
23 0.00
24 0.00
25 0.00
26 0.00
27 0.00
28 0.00
29 0.00
30 0.00
31 1.00
(31 row(s) affected)
*/