如何获取列中不同值的运行总计

I'd like to group by date range as the below example

我想按日期范围分组，如下例所示

    Date        ItemNo  Qty
==================================
1/1/2014         101    20
2/1/2014         102    10
3/1/2014         103    5
4/1/2014         104    10
1/1/2014         101    5
2/1/2014         101    10
3/1/2014         102    15
4/1/2014         104    20

I want to get the balance daily by sum the qty till that day grouped by ItemNo to be as below

我希望每天通过将数量加到按ItemNo分组的数量来得到余额，如下所示

Date        ItemNo  Qty
==================================
1/1/2014      101   25
2/1/2014      101   35
2/1/2014      102   10
3/1/2014      102   25
3/1/2014      103   5
4/1/2014      104   30

I know I can solve the problem by using cursors but I need another solution

我知道我可以通过使用游标解决问题，但我需要另一种解决方案

thanks

谢谢

4 个解决方案

#1

so just use SUM

所以只需使用SUM

SELECT Date, ItemNo, SUM(Qty)
FROM table
GROUP BY Date, ItemNo

please read on agregate function and sum

请阅读agregate函数和总和

Edit

i took your comment and did this:

我接受了你的评论并做了这个：

SELECT a.Date, a.ItemNo, tmp.qty + a.ItemNo
FROM table a
JOIN (SELECT TOP 1 * FROM table t WHERE t.date < a.Date ORDER BY t.date DESC) tmp ON a.ItemNo = tmp.ItemNo 

i'm checking it now, so it might need some tweaks, but i wanted to release it straight away so you'll have the general idea

我正在检查它，所以它可能需要一些调整，但我想立即发布它，所以你会有一般的想法

#2

Here is your sample table

这是你的样本表

SELECT * INTO #TEMP
FROM
(
SELECT  '1/1/2014' [DATE],         101 [ItemNo],    20 QTY
UNION ALL
SELECT '2/1/2014',         102,    10
UNION ALL
SELECT '3/1/2014',         103,    5
UNION ALL
SELECT '4/1/2014',         104,    10
UNION ALL
SELECT '1/1/2014',         101,    5
UNION ALL
SELECT '2/1/2014',        101,    10
UNION ALL
SELECT '3/1/2014',         102,    15
UNION ALL
SELECT '4/1/2014',         104,    20
)TAB

Use Row_Number to get number for each Item's date do the sum inside CTE

使用Row_Number获取每个Item的日期的数字，并在CTE中进行求和

;WITH CTE1 AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY  [ItemNo] ORDER BY CAST([DATE] AS DATE))RNO,
    [DATE],[ItemNo],SUM(Qty)Qty
    FROM #TEMP
    GROUP BY [DATE],[ItemNo]
)
SELECT A.RNO,[DATE],[ItemNo],
CASE WHEN RNO=1 THEN Qty 
     ELSE (SELECT SUM(b.Qty)
           FROM   CTE1 b
           WHERE  A.ItemNo=B.ItemNo AND B.RNO<=A.RNO) 
END QTY
FROM CTE1 A
ORDER  BY A.itemno,CAST(A.[DATE] AS DATE);

RESULT

结果

#3

Here's a solution using a recursive common table expression.

这是使用递归公用表表达式的解决方案。

Not sure if it will be faster or not than the answer by Sarath Avanavu, but you can try!

不确定它是否会比Sarath Avanavu的答案更快或更快，但你可以尝试！

Sample data:

样本数据：

DECLARE @t TABLE([Date] DATETIME, ItemNo INT, QTY INT)
INSERT @t
        ( Date, ItemNo, QTY )
          SELECT  '1/1/2014',         101,    20
UNION ALL SELECT '2/1/2014',         102,    10
UNION ALL SELECT '3/1/2014',         103,    5
UNION ALL SELECT '4/1/2014',         104,    10
UNION ALL SELECT '1/1/2014',         101,    5
UNION ALL SELECT '2/1/2014',        101,    10
UNION ALL SELECT '3/1/2014',         102,    15
UNION ALL SELECT '4/1/2014',         104,    20

Query:

查询：

;WITH dSum AS (
    SELECT [Date], ItemNo, SUM(QTY) AS QTY 
    FROM @t AS t
    GROUP BY [Date], [ItemNo]
), dSumRN AS (
    SELECT [Date], ItemNo, QTY, ROW_NUMBER() OVER(PARTITION BY ItemNo ORDER BY [Date]) AS rn 
    FROM dSum
), cte AS (
        SELECT [Date], ItemNo, QTY, rn
        FROM  dSumRN
        WHERE rn = 1
    UNION ALL SELECT
        dSumRN.[Date], dSumRN.ItemNo, cte.QTY + dSumRN.QTY AS QTY, cte.rn + 1 AS rn
    FROM cte
        JOIN dSumRN ON cte.ItemNo = dSumRN.ItemNo AND cte.rn + 1 = dSumRN.rn

)
SELECT [Date], [ItemNo], QTY FROM cte
ORDER BY [Date], [ItemNo]
OPTION (MAXRECURSION 1000) -- maximum this can be set to is 32767

#4

Easiest code below for your query:

以下最简单的代码用于查询：

select Date,itemno,
(select sum(Qty) from #temp where date<=T.date and itemno=T.itemno)
from #temp T 
group by Date,itemno order by date