在多列上运行合计

Question

我有一个这样的表。

Date               Item               BuyItem
20150101           Mouse              10
20150101           Keyboard           100
20150202           Mouse              20
20150202           Keyboard           200

我想这样查询。

Date               Item               RunningTotal
20150101           Mouse              10
20150202           Mouse              30
20150101           Keyboard           100
20150202           Keyboard           300

我该怎么做？递归CTE绑定在一个正在运行的列上，比如Date，对吧？现在我有一个正在运行的列Date和另一个分组列Item。请帮帮我！

Answer 1

尝试使用Cross Apply或Correlated sub-query

;with cte as
(
select 
 * from
 (VALUES (20150101,'Mouse',10),
               (20150101,'Keyboard',100),
               (20150202,'Mouse',20),
               (20150202,'Keyboard',200) )tc(Date, Item, BuyItem) 
)
SELECT *
FROM   cte a
       CROSS apply(SELECT Sum(BuyItem) AS running_total
                   FROM   cte b
                   WHERE  a.Item = b.Item
                          AND a.Date >= b.Date) cs 

结果：

Date        Item        BuyItem running_total
--------    --------    ------- -------------
20150101    Mouse       10      10
20150202    Mouse       20      30
20150101    Keyboard    100     100
20150202    Keyboard    200     300

Recursive CTE方法：

;WITH cte
     AS (SELECT Row_number()OVER(partition BY Item
                    ORDER BY date ) AS rn,*
         FROM   (VALUES (20150101,'Mouse',10),
                        (20150101,'Keyboard',100),
                        (20150202,'Mouse',20),
                        (20150202,'Keyboard',200) )tc(Date, Item, BuyItem)),
     CTE_RunningTotal
     AS (SELECT Date,Item,BuyItem,BuyItem AS running_total,rn
         FROM   cte
         WHERE  rn = 1
         UNION ALL
         SELECT T.Date,T.Item,t.BuyItem,
                T.BuyItem + C.running_total AS running_total,
                t.rn
         FROM   CTE_RunningTotal AS C
                INNER JOIN cte AS T
                        ON T.Item = c.Item
                           AND t.rn = C.rn + 1)
SELECT Date,
       Item,
       BuyItem,
       running_total
FROM   CTE_RunningTotal AS C

最好将您的服务器更新为2012，它可以使用sum() over(order by)方法来计算运行总数，这比

Answer 2

利用窗口函数。在SQL 2012+中

DECLARE @Items TABLE
(
DATE NVARCHAR(MAX),
Item NVARCHAR(MAX),
BuyItem int
)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150101', 'Mouse', 10)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150101', 'Keyboard', 100)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150202', 'Mouse', 20)
INSERT INTO @Items(DATE, Item, BuyItem) VALUES('20150202', 'Keyboard', 200)
SELECT DATE, Item, SUM(BuyItem) OVER (PARTITION BY Item ORDER BY BuyItem) AS RunningTotal FROM @Items ORDER BY Item DESC

以下是运行上述查询的输出。

20150101    Mouse       10
20150202    Mouse       30
20150101    Keyboard    100
20150202    Keyboard    300

Answer 3

在聚合函数中，随着total的运行，使用Window函数是非常好的性能

在SQL 2012+中：

SELECT *,
   SUM() OVER(PARTITION BY Item, ORDER BY Date) AS RunningTotal
FROM
Your_Table
ORDER BY Item DESC

比添加窗口框架时更快：

SELECT *,
   SUM() OVER(PARTITION BY Item, ORDER BY Date ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM
Your_Table
ORDER BY Item DESC

Answer 4

SELECT Date,
       item,
       running_total
FROM   #yourtable a
       CROSS apply(SELECT Sum(BuyItem) AS running_total
                   FROM   #yourtable b
                   WHERE  a.Item = b.Item
                          AND a.Date >= b.Date) ca
ORDER  BY BuyItem