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

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

This will give me results like:

|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!

回答1:

try this:

;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 Demo

回答2:

No join is needed, you can speed up the query this way:

select id, [user], value, total
from
(
  select id, [user], value, 
  row_number() over (partition by [user] order by id desc) rn, 
  sum(value) over (partition by [user]) total
from users
) a
where rn = 1

回答3:

add where statement:

select * from
(
your select statement
) t

where t.id in (select max(id) from table group by user)

also you can use this query:

SELECT a.id, a.user, a.value, 

(select max(b.value) from table b where b.user=a.user) AS total

FROM table a 

where a.id in (select max(id) from table group by user)

ORDER BY a.id

回答4:

Adding a right join would perform better than nested select.

Or even simpler:

SELECT MAX(id), [user], MAX(value), SUM(value)
FROM table
GROUP BY [user]

回答5:

Compatible with SQL Server 2008 or later

DECLARE @AnotherTbl TABLE
    (
        id           INT
      , somedate     DATE
      , somevalue    DECIMAL(18, 4)
      , runningtotal DECIMAL(18, 4)
    )

INSERT INTO @AnotherTbl
    (
        id
      , somedate
      , somevalue
      , runningtotal
    )
SELECT  LEDGER_ID
      , LL.LEDGER_DocDate
      , LL.LEDGER_Amount
      , NULL
FROM    ACC_Ledger LL
ORDER BY LL.LEDGER_DocDate

DECLARE @RunningTotal DECIMAL(18, 4)
SET @RunningTotal = 0

UPDATE  @AnotherTbl
SET @RunningTotal=runningtotal = @RunningTotal + somevalue
FROM    @AnotherTbl

SELECT  *
FROM    @AnotherTbl