Sql Server Sum函数的特殊使用

时间：2020-06-09来源：www.pcxitongcheng.com作者：电脑系统城

个人示例例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70 DECLARE @Sale Table
(--年份月份销售总额
[Year] INT, [Month] INT, TotalSales Float
)
insert INTO @Sale
SELECT 2016, 1, 100 UNION ALL SELECT 2016, 2, 200 UNION ALL
SELECT 2016, 3, 300 UNION ALL SELECT 2016, 4, 400 UNION ALL
SELECT 2016, 5, 500 UNION ALL SELECT 2016, 6, 600 UNION ALL
SELECT 2016, 7, 650 UNION ALL SELECT 2016, 8, 500 UNION ALL
SELECT 2016, 9, 450 UNION ALL SELECT 2016, 10, 400 UNION ALL
SELECT 2016, 11, 300 UNION ALL SELECT 2016, 12, 400 UNION ALL
SELECT 2017, 1, 500 UNION ALL SELECT 2017, 2, 400 UNION ALL
SELECT 2017, 3, 500 UNION ALL SELECT 2017, 4, 400 UNION ALL
SELECT 2017, 5, 500 UNION ALL SELECT 2017, 6, 400 UNION ALL
SELECT 2017, 7, 500 UNION ALL SELECT 2017, 8, 400 UNION ALL
SELECT 2017, 9, 500 UNION ALL SELECT 2017, 10, 400 UNION ALL
SELECT 2017, 11, 500 UNION ALL SELECT 2017, 11, 400; --注意2017有2条11月份重复数据
select [YEAR], [Month], TotalSales,
SUM(TotalSales)over() as [16-17销售总额],
--将所有的记录的TotalSales加起来的总和
--100+200+300+400+500+600+650+500+450+400+300+400+500+400+500+400+500+400+500+400+500+400+500+400 = 10200

--【未使用 partition by 指定分区示例】-------------------------------------------------------------
sum(TotalSales) over(order by [YEAR]) [列1],
--按照Year排序，计算同Year的总值与前面排序的总值之和，等同于[列3]
sum(TotalSales) over(order by [YEAR],[Month]) [列2],
--按照Year，Month排序，计算同Year，Month的总值与前面排序的总值之和,等同于[列4]
sum(TotalSales) over(order by [YEAR] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列3],
--RANGE BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为当前行与当前行前面的所有行的值
-- Year[2016]值为4800, Year[2017]值为其5400+TValue[2016]【4800】为10200
sum(TotalSales) over(order by [YEAR],[Month] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列4],
--按照Year,Month排序，累加当前行和前面所有行的值写入到同Year,Month中【注意，和列6中23，24的差异】
sum(TotalSales) over(order by [YEAR] rows BETWEEN unbounded preceding AND CURRENT ROW)[列5],
--rows BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为当前行与当前行前面的所有行的值。
--按照Year排序，把当前行的值和前面所有行的值累加
sum(TotalSales) over(order by [YEAR],[Month] rows BETWEEN unbounded preceding AND CURRENT ROW)[列6],
--按照Year,Month排序，当前行的值为当前行和排序后前面所有的行的总值，【注意，和列4中 23，24行的差异】
sum(TotalSales) over(order by [YEAR] rows BETWEEN 1 preceding AND 2 following) [列7],
--rows BETWEEN 1 preceding AND 2 following 表示指定取值范围为当前行与前一行和后两行的值
--即第一行的值为：600=100+200+300　　第二行的值为：1000=100+200+300+400　　第三行的值为：1400=200+300+400+500
sum(TotalSales) over(order by [YEAR] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [列8],
--ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示指定取值范围为当前行与前一行的值
--即第一行的值为：100=100　　第二行的值为：300=100+200　　第三行的值为：500=300+200

--【使用 partition by 指定分区示例】--------------------------------------------------------------
SUM(TotalSales)over(PARTITION BY [YEAR])[年销售额],
--按照Year进行分区，将同Year的TotalSales累积并写入到同Year的行中
--2016区间：100+200+300+400+500+600+650+500+450+400+300+400 = 4800
--2017区间：500+400+500+400+500+400+500+400+500+400+500+400=5400
SUM(TotalSales)over(PARTITION BY [YEAR],[Month])[月销售额],
--按照Year,Month分区， Year,Month组成的只有1条数据，值为TotalSales

sum(TotalSales) over(partition by [YEAR] order by [YEAR]) [列09],
--按照Year分区，2016为1个区间，2017为1个区间，按照Year排序，当前行值为同Year的总值 --和前面Year的总值[没有前面Year]
sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month]) [列10],
--按照Year分区，2016为1个区间，2017为1个区间，按照Year，Month排序排序，同Year,Month的值为[排序后前面同Year,Month的总值]加上[当前Year,Month的总值]

sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] RANGE BETWEEN unbounded preceding AND CURRENT ROW) [列11],
--按照Year分区，2016为1个区间，2017为1个区间，以[YEAR],[Month]排序，同Year,Month的值为[排序后前面同Year,Month的总值]加上[当前Year,Month的总值]
sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] rows BETWEEN unbounded preceding AND CURRENT ROW) [列12],
--按照Year分区，以[YEAR],[Month]排序，当前行的值为当前行和前面所有行的总值, 注意23，24行与列11的比较
sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] rows BETWEEN 1 preceding AND 2 following) [列13],
--按照Year分区，以[YEAR],[Month]排序，当前行的值为当前行前面1行到当前行后面2行的总值
sum(TotalSales) over(partition by [YEAR] order by [YEAR],[Month] ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [列14]
--按照Year分区，以[YEAR],[Month]排序，当前行的值为当前行前面1行到当前行的值

--ROWS与RANGE之间的区别看[列4]与[列5]，[列11]与[列12]中第23，24的值差异
from @Sale

Sql Server Sum函数的特殊使用

相关信息

热门系统总排行

装机必备

系统教程栏目

栏目热门教程

人气教程排行

站长推荐

热门系统下载