请教以下根据日期统计数据总和的sql语句写法.

[marklr]

例如今天是 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

[快溜]

select convert(varchar(10),theDate,120) theDate,sum(totalPrice) totalPrice
from tb where datediff(month,theDate,getdate())=0
group by convert(varchar(10),theDate,120)

[marklr]

convert(varchar(10),theDate,23) 和 convert(varchar(10),theDate,120) 有什么区别?

[--小F--]

select
 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)

[--小F--]

[Quote=引用 2 楼 marklr 的回复:]
convert(varchar(10),theDate,23) 和 convert(varchar(10),theDate,120) 有什么区别?
[/Quote]
一样的

[marklr]

[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code
select
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)
[/Quote]

出来的结果好像不正确啊

[marklr]

例如23号的总金额是统计包括23号以及之前所有金额的总和,然后22号那条记录就统计包括22号以及22号以前所有金额的总和

[chuanzhang5687]

create table tb
(
	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 行受影响)

[Quote=引用 6 楼 marklr 的回复:]

例如23号的总金额是统计包括23号以及之前所有金额的总和,然后22号那条记录就统计包括22号以及22号以前所有金额的总和
[/Quote]

[geniuswjt]

--> 测试数据: [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 行受影响)