例如今天是 2011-9-23日,我想得出以下结果报表,就是以数据库服务器当天日期为起点,统计最近一个月每一天及之前日期的金额总和最终结果报表:2011-9-23 68
2011-9-22 43
2011-9-21 9
数据表:theDate totalPrice2011-9-23 1
2011-9-23 2
2011-9-23 22
2011-9-22 32
2011-9-22 2
2011-9-21 9
2011-9-22 43
2011-9-21 9
数据表:theDate totalPrice2011-9-23 1
2011-9-23 2
2011-9-23 22
2011-9-22 32
2011-9-22 2
2011-9-21 9
from tb where datediff(month,theDate,getdate())=0
group by convert(varchar(10),theDate,120)
convert(varchar(10),theDate,120) as theDate,sum(totalPrice) as totalPrice
from
tb
where
datediff(mm,theDate,getdate())=0
group by
convert(varchar(10),theDate,120)
(
theDate datetime,
totalPrice int
)
insert into tb values('2011-9-23',1)
insert into tb values('2011-9-23',2)
insert into tb values('2011-9-23',22)
insert into tb values('2011-9-22',32)
insert into tb values('2011-9-22',2)
insert into tb values('2011-9-21',9)with cte as
(
select row_number() over (order by thedate) num,* from tb
)
,cte2 as
(
select thedate ,totalprice = (select sum(totalprice) from cte where num <= t.num)
from cte t
)
select convert(varchar(10),theDate,120)thedate,max(totalprice) totalprice from cte2
group by convert(varchar(10),theDate,120)
order by thedate desc
/*
thedate totalprice
---------- -----------
2011-09-23 68
2011-09-22 43
2011-09-21 9(3 行受影响)
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (theDate varchar(10),totalPrice int)
insert into [tb]
select '2011-9-23',1 union all
select '2011-9-23',2 union all
select '2011-9-23',22 union all
select '2011-9-22',32 union all
select '2011-9-22',2 union all
select '2011-9-21',9--开始查询
select theDate,
totalPrice=(select sum(totalPrice) from [tb] where theDate<=a.theDate)
from [tb] a
group by theDate
order by 1 desc--结束查询
drop table [tb]/*
theDate totalPrice
---------- -----------
2011-9-23 68
2011-9-22 43
2011-9-21 9(3 行受影响)