T-SQL 使用 SUM 计算运行总数 [英] T-SQL using SUM for a running total

问题描述

我有一个简单的表格，其中包含一些虚拟数据设置，例如:

I have a simple table with some dummy data setup like:

|id|user|value|
---------------
 1  John   2
 2  Ted    1
 3  John   4
 4  Ted    2

我可以通过执行以下 sql(MSSQL 2008) 语句来选择运行总数:

I can select a running total by executing the following sql(MSSQL 2008) statement:

SELECT a.id, a.user, a.value, SUM(b.value) AS total
FROM table a INNER JOIN table b
ON a.id >= b.id
AND a.user = b.user
GROUP BY a.id, a.user, a.value
ORDER BY a.id

这会给我如下结果:

|id|user|value|total|
---------------------
 1  John   2     2
 3  John   4     6
 2  Ted    1     1
 4  Ted    2     3

现在是否可以只为每个用户检索最近的行?所以结果是:

Now is it possible to only retrieve the most recent rows for each user? So the result would be:

|id|user|value|total|
---------------------
 3  John   4     6
 4  Ted    2     3

我这样做对吗?任何建议或遵循的新路径都会很棒！

Am I going about this the right way? any suggestions or a new path to follow would be great!

推荐答案

试试这个:

;with cte as 
     (SELECT a.id, a.[user], a.value, SUM(b.value) AS total
    FROM users a INNER JOIN users b
    ON a.id >= b.id
    AND a.[user] = b.[user]
    GROUP BY a.id, a.[user], a.value
     ),
cte1 as (select *,ROW_NUMBER() over (partition by [user] 
                         order by total desc) as row_num
         from cte)
select  id,[user],value,total from cte1 where row_num=1

SQL Fiddle 演示

这篇关于T-SQL 使用 SUM 计算运行总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！