sql – 使用分组累积先前的行

Customer    Month    Amount
-----------------------------
Tom         1        10
Kate        1        60
Ali         1        70
Tom         2        50
Kate        2        40
Tom         3        80
Ali         3        20

我希望select能够每个月积累客户

Customer    Month    Amount
-----------------------------
Tom         1        10
Kate        1        60
Ali         1        70
Tom         2        60
Kate        2        100
Ali         2        70
Tom         3        140
Kate        3        100
Ali         3        90

注意到Ali没有2个月的数据
凯特没有3个月的数据

我已经做到了,但问题是,对于每个客户缺少的月份,没有数据显示
即凯特必须在第3个月有100个金额
和阿里必须在第二个月有70个金额

declare @myTable as TABLE   (Customer varchar(50),Month int,Amount int)
;

INSERT INTO @myTable
    (Customer,Month,Amount)
VALUES
    ('Tom',1,10),('Kate',60),('Ali',70),('Tom',2,50),40),3,80),20);


select * from @myTable


select
    SUM(b.Amount),a.Customer,a.Month
from
    @myTable a
        inner join
    @myTable b
        on a.Customer = b.Customer and 
            a.Month >= b.Month
group by
    a.Customer,a.Month

解决方法

DECLARE @myTable TABLE(Customer varchar(50),Amount int);

INSERT INTO @myTable(Customer,20);


DECLARE @FullTable TABLE(Customer varchar(50),Amount int);

INSERT INTO @FullTable(Customer,Amount)
SELECT c.Customer,m.Month,ISNULL(mt.Amount,0)
FROM (SELECT DISTINCT [Month] FROM @myTable) AS m
CROSS JOIN (SELECT DISTINCT Customer FROM @myTable) AS c
LEFT JOIN @myTable AS mt ON m.Month = mt.Month AND c.Customer = mt.Customer


SELECT t1.Customer,t1.Month,t1.Amount,(t1.Amount + ISNULL(t2.sm,0)) AS AmountSum
FROM @FullTable AS t1
CROSS APPLY (SELECT SUM(Amount) AS sm FROM @FullTable AS t WHERE t.Customer = t1.Customer AND t.Month < t1.Month) AS t2
ORDER BY Month,Customer

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!