分组滚动累加求和

按分组滚动累计求和。下买呢的例子复制后在SQL查询分析器中就可以得到结果。在快表中应用时，主要是填充那，按快表的填充规则填充就可以。如果没搞定可以联系快表客服！

1. SET ANSI_NULLS ON
   
2. GO
   
3. SET QUOTED_IDENTIFIER ON
   
4. GO
   
5. CREATE TABLE [dbo].[KBOrder](
   
6.         [COMPANY] [varchar](50) NULL,
   
7.         [PERSON] [varchar](50) NULL,
   
8.         [JIE] [numeric](18, 2) NULL,
   
9.         [DAI] [numeric](18, 2) NULL,
   
10.         [YU] [numeric](18, 2) NULL
    
11. ) ON [PRIMARY]
    
12. GO
    
13. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'张三', CAST(1.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), NULL)
    
14. GO
    
15. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'张三', CAST(2.00 AS Numeric(18, 2)), CAST(9.00 AS Numeric(18, 2)), NULL)
    
16. GO
    
17. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'张三', CAST(3.00 AS Numeric(18, 2)), CAST(8.00 AS Numeric(18, 2)), NULL)
    
18. GO
    
19. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'B', N'李四', CAST(4.00 AS Numeric(18, 2)), CAST(7.00 AS Numeric(18, 2)), NULL)
    
20. GO
    
21. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'B', N'李四', CAST(5.00 AS Numeric(18, 2)), CAST(6.00 AS Numeric(18, 2)), NULL)
    
22. GO
    
23. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'王五', CAST(6.00 AS Numeric(18, 2)), CAST(5.00 AS Numeric(18, 2)), NULL)
    
24. GO
    
25. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'王五', CAST(7.00 AS Numeric(18, 2)), CAST(4.00 AS Numeric(18, 2)), NULL)
    
26. GO
    
27. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'A', N'王五', CAST(8.00 AS Numeric(18, 2)), CAST(3.00 AS Numeric(18, 2)), NULL)
    
28. GO
    
29. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'B', N'赵六', CAST(9.00 AS Numeric(18, 2)), CAST(2.00 AS Numeric(18, 2)), NULL)
    
30. GO
    
31. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [JIE], [DAI], [YU]) VALUES (N'B', N'赵六', CAST(10.00 AS Numeric(18, 2)), CAST(1.00 AS Numeric(18, 2)), NULL)
    
32. GO
    
33. 
    
34. 
    
35. SELECT [COMPANY]
    
36.       ,[PERSON]
    
37.       ,[JIE]
    
38.       ,[DAI]
    
39.       ,[YU]
    
40.           ,SUM([JIE] - [DAI]) OVER(PARTITION BY COMPANY,PERSON
    
41.                                                                 ORDER BY COMPANY,PERSON
    
42.                                                                 ROWS BETWEEN UNBOUNDED PRECEDING
    
43.                                                                          AND CURRENT ROW) AS RunSum
    
44.   FROM [dbo].[KBOrder]

复制代码

1. DROP TABLE [dbo].[KBOrder]
   
2. SET ANSI_NULLS ON
   
3. GO
   
4. SET QUOTED_IDENTIFIER ON
   
5. GO
   
6. CREATE TABLE [dbo].[KBOrder](
   
7.         [COMPANY] [varchar](50) NULL,
   
8.         [PERSON] [varchar](50) NULL,
   
9.         [JIE] [numeric](18, 2) NULL,
   
10.         [DAI] [numeric](18, 2) NULL,
    
11.         [YU] [numeric](18, 2) NULL
    
12. ) ON [PRIMARY]
    
13. GO
    
14. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(1.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), NULL)
    
15. GO
    
16. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(2.00 AS Numeric(18, 2)), CAST(9.00 AS Numeric(18, 2)), NULL)
    
17. GO
    
18. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(3.00 AS Numeric(18, 2)), CAST(8.00 AS Numeric(18, 2)), NULL)
    
19. GO
    
20. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'李四', CAST(4.00 AS Numeric(18, 2)), CAST(7.00 AS Numeric(18, 2)), NULL)
    
21. GO
    
22. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'李四', CAST(5.00 AS Numeric(18, 2)), CAST(6.00 AS Numeric(18, 2)), NULL)
    
23. GO
    
24. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(6.00 AS Numeric(18, 2)), CAST(5.00 AS Numeric(18, 2)), NULL)
    
25. GO
    
26. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(7.00 AS Numeric(18, 2)), CAST(4.00 AS Numeric(18, 2)), NULL)
    
27. GO
    
28. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(8.00 AS Numeric(18, 2)), CAST(3.00 AS Numeric(18, 2)), NULL)
    
29. GO
    
30. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'赵六', CAST(9.00 AS Numeric(18, 2)), CAST(2.00 AS Numeric(18, 2)), NULL)
    
31. GO
    
32. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'赵六', CAST(10.00 AS Numeric(18, 2)), CAST(1.00 AS Numeric(18, 2)), NULL)
    
33. GO
    
34. 
    
35. WITH C AS(
    
36. SELECT [COMPANY]
    
37.       ,[PERSON]
    
38.       ,[JIE]
    
39.       ,[DAI]
    
40.       ,[YU]
    
41.           ,SUM([JIE]) OVER(PARTITION BY COMPANY,PERSON
    
42.                                                                 ORDER BY COMPANY,PERSON
    
43.                                                                 ROWS BETWEEN UNBOUNDED PRECEDING
    
44.                                                                 AND CURRENT ROW) RunSumJIE
    
45.           ,SUM(DAI) OVER(PARTITION BY COMPANY,PERSON ORDER BY COMPANY,PERSON) SumDai
    
46. 
    
47.           ,SUM([JIE]) OVER(PARTITION BY COMPANY,PERSON
    
48.                                                                 ORDER BY COMPANY,PERSON
    
49.                                                                 ROWS BETWEEN UNBOUNDED PRECEDING
    
50.                                                                 AND CURRENT ROW)
    
51.           - SUM(DAI) OVER(PARTITION BY COMPANY,PERSON ORDER BY COMPANY,PERSON) CHA
    
52. 
    
53.   FROM [dbo].[KBOrder]
    
54.   )
    
55.   SELECT C.COMPANY,C.PERSON,C.JIE,C.DAI,C.YU,C.RunSumJIE,C.SumDai,C.CHA
    
56.   ,CASE WHEN C.CHA <= 0 THEN 0 WHEN C.CHA > 0 AND C.CHA < C.JIE THEN C.CHA WHEN C.CHA > 0 AND C.CHA > C.JIE THEN C.JIE END
    
57.   FROM C

复制代码

1. DROP TABLE [dbo].[KBOrder]
   
2. SET ANSI_NULLS ON
   
3. GO
   
4. SET QUOTED_IDENTIFIER ON
   
5. GO
   
6. CREATE TABLE [dbo].[KBOrder](
   
7.         [COMPANY] [varchar](50) NULL,
   
8.         [PERSON] [varchar](50) NULL,
   
9.         [JIE] [numeric](18, 2) NULL,
   
10.         [DAI] [numeric](18, 2) NULL,
    
11.         [YU] [numeric](18, 2) NULL
    
12. ) ON [PRIMARY]
    
13. GO
    
14. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(1.00 AS Numeric(18, 2)), CAST(10.00 AS Numeric(18, 2)), NULL)
    
15. GO
    
16. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(2.00 AS Numeric(18, 2)), CAST(9.00 AS Numeric(18, 2)), NULL)
    
17. GO
    
18. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'张三', CAST(3.00 AS Numeric(18, 2)), CAST(8.00 AS Numeric(18, 2)), NULL)
    
19. GO
    
20. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'李四', CAST(4.00 AS Numeric(18, 2)), CAST(7.00 AS Numeric(18, 2)), NULL)
    
21. GO
    
22. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'李四', CAST(5.00 AS Numeric(18, 2)), CAST(6.00 AS Numeric(18, 2)), NULL)
    
23. GO
    
24. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(6.00 AS Numeric(18, 2)), CAST(5.00 AS Numeric(18, 2)), NULL)
    
25. GO
    
26. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(7.00 AS Numeric(18, 2)), CAST(4.00 AS Numeric(18, 2)), NULL)
    
27. GO
    
28. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'A', N'王五', CAST(8.00 AS Numeric(18, 2)), CAST(3.00 AS Numeric(18, 2)), NULL)
    
29. GO
    
30. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'赵六', CAST(9.00 AS Numeric(18, 2)), CAST(2.00 AS Numeric(18, 2)), NULL)
    
31. GO
    
32. INSERT [dbo].[KBOrder] ([COMPANY], [PERSON], [DAI], [JIE], [YU]) VALUES (N'B', N'赵六', CAST(10.00 AS Numeric(18, 2)), CAST(1.00 AS Numeric(18, 2)), NULL)
    
33. GO
    
34. 
    
35. WITH C AS(
    
36. SELECT [COMPANY]
    
37.       ,[PERSON]
    
38.       ,[JIE]
    
39.       ,[DAI]
    
40.       ,[YU]
    
41.           ,SUM([JIE]) OVER(PARTITION BY COMPANY,PERSON
    
42.                                                                 ORDER BY COMPANY,PERSON
    
43.                                                                 ROWS BETWEEN UNBOUNDED PRECEDING
    
44.                                                                 AND CURRENT ROW) RunSumJIE
    
45.           ,SUM(DAI) OVER(PARTITION BY COMPANY,PERSON ORDER BY COMPANY,PERSON) SumDai
    
46. 
    
47.           ,SUM([JIE]) OVER(PARTITION BY COMPANY,PERSON
    
48.                                                                 ORDER BY COMPANY,PERSON
    
49.                                                                 ROWS BETWEEN UNBOUNDED PRECEDING
    
50.                                                                 AND CURRENT ROW)
    
51.           - SUM(DAI) OVER(PARTITION BY COMPANY,PERSON ORDER BY COMPANY,PERSON) CHA
    
52. 
    
53.   FROM [dbo].[KBOrder]
    
54.   )
    
55.   SELECT C.COMPANY,C.PERSON,C.JIE,C.DAI,C.YU,C.RunSumJIE,C.SumDai,C.CHA
    
56.   ,CASE WHEN C.CHA <= 0 THEN 0 WHEN C.CHA > 0 AND C.CHA < C.JIE THEN C.CHA WHEN C.CHA > 0 AND C.CHA > C.JIE THEN C.JIE END
    
57.   FROM C

复制代码