按月份显示数据的值 查询某月的值,该如何处理

按月份显示数据的值 查询某月的值
表 DSF

id Dtime VAL

1 2012-1-2 2.06

2 2012-1-2 3.00
.....

3 2012-1-31 1.00

要得到的结果是  

M_Day val
1 0

2 5.06

3 0
....
31 1

[解决办法]

SQL code

declare @DSF table (id int,Dtime datetime,VAL numeric(3,2))insert into @DSFselect 1,'2012-1-2',2.06 union allselect 2,'2012-1-2',3.00 union allselect 3,'2012-1-31',1.00select b.number,sum(isnull(a.VAL,0)) as VAL from @DSF a right join master..spt_values b on day(a.Dtime)=b.numberwhere b.type='p' and b.number between 1 and 31 group by b.number/*number      VAL----------- ---------------------------------------1           0.002           5.063           0.004           0.005           0.006           0.007           0.008           0.009           0.0010          0.0011          0.0012          0.0013          0.0014          0.0015          0.0016          0.0017          0.0018          0.0019          0.0020          0.0021          0.0022          0.0023          0.0024          0.0025          0.0026          0.0027          0.0028          0.0029          0.0030          0.0031          1.00(31 row(s) affected)*/
[解决办法]
探讨

引用:
SQL code

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……