例如今天是 2011-9-23日,我想得出以下结果报表,就是以数据库服务器当天日期为起点,统计最近一个月每一天及之前日期的金额总和
最终结果报表:
2011-9-23 68
2011-9-22 43
2011-9-21 9
数据表:
theDate totalPrice
2011-9-23 1
2011-9-23 2
2011-9-23 22
2011-9-22 32
2011-9-22 2
2011-9-21 9
------解决方案--------------------
- SQL code
--> 测试数据: [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 allselect '2011-9-23',2 union allselect '2011-9-23',22 union allselect '2011-9-22',32 union allselect '2011-9-22',2 union allselect '2011-9-21',9--开始查询select theDate,totalPrice=(select sum(totalPrice) from [tb] where theDate<=a.theDate) from [tb] agroup by theDateorder by 1 desc--结束查询drop table [tb]/*theDate totalPrice---------- -----------2011-9-23 682011-9-22 432011-9-21 9(3 行受影响)