使用SUM进行总计的T-SQL

Question

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

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

我可以通过执行以下sql（MSSQL 2008）语句来选择运行总计：

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

现在可以只为每个用户检索最新的行吗？因此结果将是：

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

我要这样做正确吗？任何建议或遵循的新路径都将是很棒的！

Answer 1

试试这个：

;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小提琴演示