sqlserver运行总问题 [英] sqlserver running total problem

问题描述

我有一张桌子
喜欢:-
id somedate somevalue
--------- ---------
45 01/Jan/09 3
23 08/Jan/09 5
12 02/Feb/09 0
77 14/Feb/09 7
39 20/Feb/09 34
33 02/Mar/09 6

我想像这样打印:-
id somedate somevalue runningtotal
--------- --------- ------------
45 01/Jan/09 3 3
23 08/Jan/09 5 8
12 02/Feb/09 0 8
77 14/Feb/09 7 15
39 20/Feb/09 34 49
33 02/Mar/09 6 55


请帮帮我.

[edit]添加了代码块以保留格式-OriginalGriff [/edit]

I have a table
like:-
id somedate somevalue
-- -------- ---------
45 01/Jan/09 3
23 08/Jan/09 5
12 02/Feb/09 0
77 14/Feb/09 7
39 20/Feb/09 34
33 02/Mar/09 6

I want To Print Like:-
id somedate somevalue runningtotal
-- -------- --------- ------------
45 01/Jan/09 3 3
23 08/Jan/09 5 8
12 02/Feb/09 0 8
77 14/Feb/09 7 15
39 20/Feb/09 34 49
33 02/Mar/09 6 55


please help me.

[edit]Code block added to preserve formatting - OriginalGriff[/edit]

推荐答案

另一种可能的解决方案，例如:

Another possible solution, something like:

SELECT a.id, 
       a.narration,
       a.dr, 
       a.cr, 
       SUM(c.calc) AS total
FROM   sometable a,
       (SELECT b.id,
               (b.dr - b.cr) AS calc
        FROM sometable b) c
WHERE c.id <= a.id
GROUP BY a.id,
         a.narration,
         a.dr,
         a.cr;

这是我为您解决的问题的解决方案:

This is the solution i made to your problem :

CREATE TABLE TEMP (id INT identity(1,1), somedate datetime, somevalue int)

insert into temp values ('01/Jan/09',3)
insert into temp values ('08/Jan/09',5)
insert into temp values ('02/Feb/09',0)
insert into temp values ('14/Feb/09',7)
insert into temp values ('20/Feb/09',34)
insert into temp values ('02/Mar/09',6)

select * from temp

create table #temp (id int, somevalue int)

insert into #temp (id, somevalue)
select id, somevalue from temp

select * from #temp

select t.id , convert(varchar,t.somedate, 106), t.somevalue, SUM(tp.somevalue) from temp t, #temp tp
where t.id >= tp.id
group by t.id , t.somedate, t.somevalue

drop table #temp

讨厌！但可能:

Nasty! But possible:

SELECT ID, narration dr, cr, (SELECT SUM(dr-cr) from myTable where iD < M.iD) as RunTot FROM myTable M

这篇关于sqlserver运行总问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！