在SQL语句中按行运行总行 [英] Running Total Row By Row in SQL Statement
问题描述
我有一个包含三个字段Id,Name和Mark的表。我有所有字段的值如下。
Id名称标记
--- ----- -----
1 aaaa 10
2 bbbb 20
3 cccc 30
现在我想获得该表的结果集如
Id Name Mark
--- ----- --- -
1 aaaa 10
2 bbbb 30
3 cccc 60
< blockquote>一种方法是使用游标。它很容易理解,但死得很慢。
你可以使用一个查询,只增加id比自己少的行:
选择
a.Id,
a.Name,
( 选择
总和(b.Mark)
来自 表 b
其中 b.Id< = a.Id) as 标记
来自 table a
但我不喜欢这个原因有几个原因。它不是一个非常可扩展的。
尝试递归CTE。它似乎是最有效的选择。
我会很快找到一个时间写一个,我会更新这个解决方案(除非别人先做了^ _ ^
更新:这是CTE。
声明 @ table table (Id int 身份( 1 , 1 ) primary key ,Name nvarchar (< span class =code-digit> 4 ),Mark int )
插入 进入 @ table (姓名,标记)
值
(' aaaa', 10 ),
(' bbbb', 20 ),
('' cccc', 30 ),
( ' dddd', 40 ),
(' eeee', 50 )
;
mycte as (
SELECT Id,Name,Mark,Mark as RunningTotal
FROM @ table
WHERE Id = 1
UNION ALL
SELECT y.Id,y.Name,y.Mark,x.RunningTotal + y.Mark
FROM mycte x
INNER JOIN @ table AS y
ON y.Id = x.Id + 1 )
选择 * 来自 mycte
结果:
Id,Name,Mark,RunningTotal
1 aaaa 10 10
2 bbbb 20 30
3 cccc 30 60
4 dddd 40 100
5 eeee 50 150
此解决方案更具可扩展性,但要注意总数是否> int.MaxValue
它被称为运行总和 [ ^ ]。请点击链接以了解如何实施它。
检查此链接。它详细解释了运行总问题 -
计算sql server中的运行总计
I have a table with three fields Id, Name and Mark. I have values of all fields like as follows.
Id Name Mark
--- ----- -----
1 aaaa 10
2 bbbb 20
3 cccc 30
Now I want to get the results set of that table like as
Id Name Mark
--- ----- -----
1 aaaa 10
2 bbbb 30
3 cccc 60
One way would be to use a cursor. It's simple to understand but dead slow.
You could use a query that adds up only rows with id's less that itself:
Select a.Id, a.Name, (Select Sum(b.Mark) from table b where b.Id <= a.Id) as Mark from table a
But I don't like that for several reason. It isn't very scale-able for one.
Try a recursive CTE. It appears to be the most efficient option.
I'll find some time shortly to write one and I'll update this solution (unless someone else does it first ^_^
UPDATE: Here's the CTE.
declare @table table(Id int Identity(1,1) primary key, Name nvarchar(4), Mark int) insert into @table (Name, Mark) values ('aaaa',10), ('bbbb',20), ('cccc',30), ('dddd',40), ('eeee',50) ; with mycte as ( SELECT Id, Name, Mark, Mark as RunningTotal FROM @table WHERE Id = 1 UNION ALL SELECT y.Id, y.Name, y.Mark, x.RunningTotal + y.Mark FROM mycte x INNER JOIN @table AS y ON y.Id = x.Id + 1) select * from mycte
Result:
Id, Name, Mark, RunningTotal 1 aaaa 10 10 2 bbbb 20 30 3 cccc 30 60 4 dddd 40 100 5 eeee 50 150
This solution is more scale-able, but watch out if total becomes > int.MaxValue
It's called running sum[^]. Follow the link to find out how to implement it.
Check this link. It has explained running total problem in detail -
Calculate a running total in sql server
这篇关于在SQL语句中按行运行总行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!