求一sql语句，急！！！
Id price time
----------- ----------- -----------------------
...
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
3 44 2013-02-09 09:05:14.000
4 12 2013-03-09 09:05:14.000
5 31 2013-04-09 09:05:14.000
6 525 2013-05-09 09:05:14.000
7 188 2013-06-13 09:12:46.000
...
...
...
我想查出详细+每个月总和+到这个月总和，有什么好方法，求大神。。。
编号 价格 时间
----------- ----------- -----------------------
1 87 2013-01-08 16:31:15.000
2 140 2013-01-09 09:05:14.000
本月小结:227 2013-01-31
累计：227 2013-01-31
3 44 2013-02-09 09:05:14.000
本月小结:44 2013-02-28
累计：271 2013-02-28
4 12 2013-03-09 09:05:14.000
本月小结:12 2013-03-31
累计：283 2013-03-31
5 31 2013-04-09 09:05:14.000
.....
..... SQL
[解决办法]
with a1 (Id,price,time) as
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
)
,a2 as
(
select convert(char(7),time,20) time,sum(price) price from a1 group by convert(char(7),time,20)
)
,a3 as
(
select time,(select sum(price) from a2 where time<=a.time) price from a2 a
)
select id,'' descr,price,time from a1
union all
select 0,'本月小结',price,dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a2
union all
select 0,'累计',price,dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a3
order by time
[解决办法]
--好语句如下：
with a1 (Id,price,time) as
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
)
,a2 as
(
select convert(char(7),time,20) time,sum(price) price from a1 group by convert(char(7),time,20)
)
,a3 as
(
select time,(select sum(price) from a2 where time<=a.time) price from a2 a
)
select CAST(id AS varchar) id,CAST(price AS VARCHAR) price,time from a1
union all
select '','本月小结:'+CAST(price AS VARCHAR),dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a2
union all
select '','累计:'+CAST(price AS VARCHAR),dateadd(dd,-1,dateadd(mm,1,cast(time+'-01' as datetime))) from a3
order by time
[解决办法]
with a1 (Id,price,time) as
(
select 1,87,'2013-01-08 16:31:15.000' union all
select 2,140,'2013-01-09 09:05:14.000' union all
select 3,44,'2013-02-09 09:05:14.000' union all
select 4,12,'2013-03-09 09:05:14.000' union all
select 5,31,'2013-04-09 09:05:14.000' union all
select 6,525,'2013-05-09 09:05:14.000' union all
select 7,188,'2013-06-13 09:12:46.000'
) --select * From a1
select b.*,
(select sum(Price) from a1 where Convert(varchar(7),time,120)=Convert(varchar(7),b.time,120) ) as [本月小结] ,
(select sum(Price) from a1 where Convert(varchar(7),time,120)<=Convert(varchar(7),b.time,120) ) as [累计]
from a1 b
