我在MS sql Server上有这个表
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
