MySQL窗口函数OVER()
下面的讲解将基于这个employee2表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
mysql> SELECT * FROM employee2;+----+-----------+------+---------+---------+| id | name | age | salary | dept_id |+----+-----------+------+---------+---------+| 3 | 小肖 | 29 | 30000.0 | 1 || 4 | 小东 | 30 | 40000.0 | 2 || 6 | 小非 | 24 | 23456.0 | 3 || 7 | 晓飞 | 30 | 15000.0 | 4 || 8 | 小林 | 23 | 24000.0 | NULL || 10 | 小五 | 20 | 4500.0 | NULL || 11 | 张山 | 24 | 40000.0 | 1 || 12 | 小肖 | 28 | 35000.0 | 2 || 13 | 李四 | 23 | 50000.0 | 1 || 17 | 王武 | 24 | 56000.0 | 2 || 18 | 猪小屁 | 2 | 56000.0 | 2 || 19 | 小玉 | 25 | 58000.0 | 1 || 21 | 小张 | 23 | 50000.0 | 1 || 22 | 小胡 | 25 | 25000.0 | 2 || 96 | 小肖 | 19 | 35000.0 | 1 || 97 | 小林 | 20 | 20000.0 | 2 |+----+-----------+------+---------+---------+16 rows in set (0.00 sec) |
窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
-
OVER()
这时候，是一个空子句，此时的效果和没有使用OVER()函数是一样的，作用的是这个表所有数据构成的窗口。
|
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
|
mysql> SELECT -> name, -> salary, -> MAX(salary) OVER() AS max_salary -- 作用于一整个窗口，此时返回的是所有数据中的MAX(salary),表示所有员工的最大工资 -> FROM employee2;+-----------+---------+------------+| name | salary | max_salary |+-----------+---------+------------+| 小肖 | 30000.0 | 58000.0 || 小东 | 40000.0 | 58000.0 || 小非 | 23456.0 | 58000.0 || 晓飞 | 15000.0 | 58000.0 || 小林 | 24000.0 | 58000.0 || 小五 | 4500.0 | 58000.0 || 张山 | 40000.0 | 58000.0 || 小肖 | 35000.0 | 58000.0 || 李四 | 50000.0 | 58000.0 || 王武 | 56000.0 | 58000.0 || 猪小屁 | 56000.0 | 58000.0 || 小玉 | 58000.0 | 58000.0 || 小张 | 50000.0 | 58000.0 || 小胡 | 25000.0 | 58000.0 || 小肖 | 35000.0 | 58000.0 || 小林 | 20000.0 | 58000.0 |+-----------+---------+------------+16 rows in set (0.00 sec)mysql> SELECT -> name, -> salary, -> MAX(salary) OVER() -- 获取部门为1的所有员工的name,salary以及这个部门的最大工资 -> FROM employee2 -> WHERE dept_id = 1;+--------+---------+--------------------+| name | salary | MAX(salary) OVER() |+--------+---------+--------------------+| 小肖 | 30000.0 | 58000.0 || 张山 | 40000.0 | 58000.0 || 李四 | 50000.0 | 58000.0 || 小玉 | 58000.0 | 58000.0 || 小张 | 50000.0 | 58000.0 || 小肖 | 35000.0 | 58000.0 |+--------+---------+--------------------+6 rows in set (0.00 sec) |
-
OVER(PARTITION BY yyy1,yyy2,yyy3)
含有了PARTITION BY 子句，此时就会根据yyy1,yyy2,yyy3这些列构成的整体进行划分窗口，只有这些列构成的整体相同，才会处在同一个窗口中。
|
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
|
mysql> SELECT -> name, -> salary, -> MAX(salary) OVER(PARTITION BY dept_id) AS dept_max_salary -- 利用了PARTITION BY ，从而根据dept_id进行分组，然后获取每个分组的最大值 -> FROM employee2;+-----------+---------+-----------------+| name | salary | dept_max_salary |+-----------+---------+-----------------+| 小林 | 24000.0 | 24000.0 | --| 分组为NULL的| 小五 | 4500.0 | 24000.0 | --|| 小肖 | 30000.0 | 58000.0 | -----|| 张山 | 40000.0 | 58000.0 || 李四 | 50000.0 | 58000.0 | -- 分组为dept_id = 1的| 小玉 | 58000.0 | 58000.0 || 小张 | 50000.0 | 58000.0 || 小肖 | 35000.0 | 58000.0 | -----|| 小东 | 40000.0 | 56000.0 | ---------|| 小肖 | 35000.0 | 56000.0 || 王武 | 56000.0 | 56000.0 || 猪小屁 | 56000.0 | 56000.0 | -- 分组为dept_id = 2的| 小胡 | 25000.0 | 56000.0 || 小林 | 20000.0 | 56000.0 | ---------|| 小非 | 23456.0 | 23456.0 | -- ------------| 分组为dept_id = 3的| 晓飞 | 15000.0 | 15000.0 | -- --------------| 分组为dept_id = 4的+-----------+---------+-----------------+16 rows in set (0.00 sec) |
-
OVER(ORDER BY yyy1,yyy2,yyy3 ASC\DESC)
每个窗口中利用ORDER BY子句，这时候将按照yyy1进行对应的升序\降序的顺序进行排序,如果yyy1相同，将根据yyy2排序（和ORDER BY 的用法一样)，这时候不仅会进行排序操作，如果是SUM与其连用的话，同时进行了累加的操作，即值是当前行加上前一行对应的值。但是下面的例子中却发现ORDER BY 后面对应的值相同的时候，并不是当前这一行加上以前行的值，例如ORDER BY salary\ORDER BY name的时候。
|
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
|
mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(ORDER BY salary) AS already_paid_salary -- 利用ORDER BY ，窗口中对应的行将按照salary进行升序排序，然后调用SUM聚集 函数，不同的窗口进行累计 -> FROM employee2;+-----------+---------+---------------------+| name | salary | already_paid_salary |+-----------+---------+---------------------+| 小五 | 4500.0 | 4500.0 || 晓飞 | 15000.0 | 19500.0 | | 小林 | 20000.0 | 39500.0 || 小非 | 23456.0 | 62956.0 || 小林 | 24000.0 | 86956.0 || 小胡 | 25000.0 | 111956.0 || 小肖 | 30000.0 | 141956.0 || 小肖 | 35000.0 | 211956.0 | -- -----| 这两行同处相同，此时这个窗口的already_paid_salary| 小肖 | 35000.0 | 211956.0 | -- -----| = (35000 * 2) (当前两行) + 141956(前面的行)| 小东 | 40000.0 | 291956.0 | -- ---| 这两行同处相同，此时这个窗口的already_paid_salary | 张山 | 40000.0 | 291956.0 | -- ---| = （40000 * 2）（当前两行） + 211956（之前行的）| 李四 | 50000.0 | 391956.0 | -- | 道理同上| 小张 | 50000.0 | 391956.0 | -- || 王武 | 56000.0 | 503956.0 | -- ------|道理同上| 猪小屁 | 56000.0 | 503956.0 | -- ------|| 小玉 | 58000.0 | 561956.0 |+-----------+---------+---------------------+16 rows in set (0.00 sec)mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(ORDER BY name) -- 每个窗口的所有行将根据name进行升序排序这时候，然后不同name的行将会进行累计操作,直接是当前行+以嵌行的，相同的时候，是相同行的和加上之前行的值 -> FROM employee2;+-----------+---------+---------------------------------+| name | salary | SUM(salary) OVER(ORDER BY name) |+-----------+---------+---------------------------------+| 小东 | 40000.0 | 40000.0 || 小五 | 4500.0 | 44500.0 || 小张 | 50000.0 | 94500.0 || 小林 | 24000.0 | 138500.0 | -- |这两组同处相同，所以对应的值为(24000 + 20000)（相同的两行） + 94500（之前的行）| 小林 | 20000.0 | 138500.0 | -- | | 小玉 | 58000.0 | 196500.0 || 小肖 | 30000.0 | 296500.0 | -- ---|这两组同处相同，所以对应的值为(30000 + 35000 + 35000)（相同的三行） + 196500（之前的行）| 小肖 | 35000.0 | 296500.0 | | 小肖 | 35000.0 | 296500.0 | -- ---|| 小胡 | 25000.0 | 321500.0 || 小非 | 23456.0 | 344956.0 || 张山 | 40000.0 | 384956.0 || 晓飞 | 15000.0 | 399956.0 || 李四 | 50000.0 | 449956.0 || 猪小屁 | 56000.0 | 505956.0 || 王武 | 56000.0 | 561956.0 |+-----------+---------+---------------------------------+16 rows in set (0.00 sec) |
同时值得注意的是，OVER()是一个全局函数，所以在使用ORDER BY 的时候，那么最后输出的时候也将是按照这个有序输出，但是仅仅在没有使用PARTITION BY的情况才是这样的.这个可以从PARTITION BY进行说明，没有使用PARTITION BY的时候，ORVER()中的ORDER BY将是针对整张表进行排序的，所以这时候如果FROM子句后面的ORDER BY后的字段和OVER()中ORDER BY后的字段相同的时候，就会显得多此一举了。
即
|
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
# 下面两个代码是一样的，但是仅仅OVER()只使用ORDER BY子句的时候，并且才这样# 两个ORDER BY后面的字段是相同才可以保证效果一样# 如果使用了PARTITION BY子句，那么OVER()中的ORDER BY将是针对每一个窗口# 中的所有行进行排序的，而在FROM子句后面的ORDER BY将是针对整张表，所以# 导致结果不同SELECT name, SUM(salary) OVER(ORDER BY NAME)FROM employee2;SELECT name, SUM(salary) OVER(ORDER BY NAME)FROM employee2ORDER BY name;mysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2;+-----------+---------+---------------------------------+| name | salary | SUM(salary) OVER(ORDER BY name) |+-----------+---------+---------------------------------+| 小东 | 40000.0 | 40000.0 || 小五 | 4500.0 | 44500.0 || 小张 | 50000.0 | 94500.0 || 小林 | 24000.0 | 138500.0 || 小林 | 20000.0 | 138500.0 || 小玉 | 58000.0 | 196500.0 || 小肖 | 30000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小胡 | 25000.0 | 321500.0 || 小非 | 23456.0 | 344956.0 || 张山 | 40000.0 | 384956.0 || 晓飞 | 15000.0 | 399956.0 || 李四 | 50000.0 | 449956.0 || 猪小屁 | 56000.0 | 505956.0 || 王武 | 56000.0 | 561956.0 |+-----------+---------+---------------------------------+16 rows in set (0.00 sec)# 两个ORDER BY后面的字段相同时，作用就会相当只使用SUM(salary) OVER(ORDER BY name)mysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2 -> ORDER BY name;+-----------+---------+---------------------------------+| name | salary | SUM(salary) OVER(ORDER BY name) |+-----------+---------+---------------------------------+| 小东 | 40000.0 | 40000.0 || 小五 | 4500.0 | 44500.0 || 小张 | 50000.0 | 94500.0 || 小林 | 24000.0 | 138500.0 || 小林 | 20000.0 | 138500.0 || 小玉 | 58000.0 | 196500.0 || 小肖 | 30000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小胡 | 25000.0 | 321500.0 || 小非 | 23456.0 | 344956.0 || 张山 | 40000.0 | 384956.0 || 晓飞 | 15000.0 | 399956.0 || 李四 | 50000.0 | 449956.0 || 猪小屁 | 56000.0 | 505956.0 || 王武 | 56000.0 | 561956.0 |+-----------+---------+---------------------------------+16 rows in set (0.00 sec)# 两个ORDER BY后的字段不同，那么FROM 子句后的ORDER BY将会覆盖OVER()中的ORDER BYmysql> SELECT -> name,salary, -> SUM(salary) OVER(ORDER BY name) -> FROM employee2 -> ORDER BY salary;+-----------+---------+---------------------------------+| name | salary | SUM(salary) OVER(ORDER BY name) |+-----------+---------+---------------------------------+| 小五 | 4500.0 | 44500.0 || 晓飞 | 15000.0 | 399956.0 || 小林 | 20000.0 | 138500.0 || 小非 | 23456.0 | 344956.0 || 小林 | 24000.0 | 138500.0 || 小胡 | 25000.0 | 321500.0 || 小肖 | 30000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小肖 | 35000.0 | 296500.0 || 小东 | 40000.0 | 40000.0 || 张山 | 40000.0 | 384956.0 || 小张 | 50000.0 | 94500.0 || 李四 | 50000.0 | 449956.0 || 猪小屁 | 56000.0 | 505956.0 || 王武 | 56000.0 | 561956.0 || 小玉 | 58000.0 | 196500.0 |+-----------+---------+---------------------------------+16 rows in set (0.00 sec)# OVER()中的ORDER BY针对的窗口中的所有行进行排序的，而下面的FROM子句中的# ORDER BY是针对整个表的，所以此时两者的作用并不相同mysql> SELECT -> name,salary, -> SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) -> FROM employee2;+-----------+---------+------------------------------------------------------+| name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) |+-----------+---------+------------------------------------------------------+| 小五 | 4500.0 | 4500.0 || 小林 | 24000.0 | 28500.0 || 小张 | 50000.0 | 50000.0 || 小玉 | 58000.0 | 108000.0 || 小肖 | 30000.0 | 173000.0 || 小肖 | 35000.0 | 173000.0 || 张山 | 40000.0 | 213000.0 || 李四 | 50000.0 | 263000.0 || 小东 | 40000.0 | 40000.0 || 小林 | 20000.0 | 60000.0 || 小肖 | 35000.0 | 95000.0 || 小胡 | 25000.0 | 120000.0 || 猪小屁 | 56000.0 | 176000.0 || 王武 | 56000.0 | 232000.0 || 小非 | 23456.0 | 23456.0 || 晓飞 | 15000.0 | 15000.0 |+-----------+---------+------------------------------------------------------+16 rows in set (0.00 sec)mysql> SELECT -> name,salary, -> SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) -> FROM employee2 -> ORDER BY name;+-----------+---------+------------------------------------------------------+| name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) |+-----------+---------+------------------------------------------------------+| 小东 | 40000.0 | 40000.0 || 小五 | 4500.0 | 4500.0 || 小张 | 50000.0 | 50000.0 || 小林 | 24000.0 | 28500.0 || 小林 | 20000.0 | 60000.0 || 小玉 | 58000.0 | 108000.0 || 小肖 | 30000.0 | 173000.0 || 小肖 | 35000.0 | 173000.0 || 小肖 | 35000.0 | 95000.0 || 小胡 | 25000.0 | 120000.0 || 小非 | 23456.0 | 23456.0 || 张山 | 40000.0 | 213000.0 || 晓飞 | 15000.0 | 15000.0 || 李四 | 50000.0 | 263000.0 || 猪小屁 | 56000.0 | 176000.0 || 王武 | 56000.0 | 232000.0 |+-----------+---------+------------------------------------------------------+16 rows in set (0.00 sec) |
-
OVER(PARTITION BY yyy ORDER BY zzz ASC\DESC)
根据PARTITION BY ，此时表示根据yyy进行分组，然后在每个窗口中的所有行将利用ORDER BY 子句，将根据zzz进行排序。值得注意的是，如果zzz和yyy相同的时候，这时候作用相当于OVER(PARTITION BY yyy),和没有ORDER BY子句是一样的，因为都处在一个窗口了。
|
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
71
72
73
74
75
76
77
78
79
|
mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(PARTITION BY dept_id) -> FROM employee2; +-----------+---------+----------------------------------------+ | name | salary | SUM(salary) OVER(PARTITION BY dept_id) | +-----------+---------+----------------------------------------+ | 小林 | 24000.0 | 28500.0 | -- | 分组为dept_id = NULL的 | 小五 | 4500.0 | 28500.0 | -- | | 小肖 | 30000.0 | 263000.0 | ------| | 张山 | 40000.0 | 263000.0 | | 李四 | 50000.0 | 263000.0 | | 小玉 | 58000.0 | 263000.0 | -- 分组为dept_id = 1的 | 小张 | 50000.0 | 263000.0 | | 小肖 | 35000.0 | 263000.0 | ------| | 小东 | 40000.0 | 232000.0 | --------| | 小肖 | 35000.0 | 232000.0 | | 王武 | 56000.0 | 232000.0 | | 猪小屁 | 56000.0 | 232000.0 | -- 分组为dept_id = 2的 | 小胡 | 25000.0 | 232000.0 | | 小林 | 20000.0 | 232000.0 | --------| | 小非 | 23456.0 | 23456.0 | -- ---------| 分组为dept_id = 3的 | 晓飞 | 15000.0 | 15000.0 | -- ------------| 分组为dept_id = 4的 +-----------+---------+----------------------------------------+ 16 rows in set (0.00 sec) mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(PARTITION BY dept_id ORDER BY dept_id) -> FROM employee2; +-----------+---------+---------------------------------------------------------+ | name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY dept_id) | +-----------+---------+---------------------------------------------------------+ | 小林 | 24000.0 | 28500.0 | | 小五 | 4500.0 | 28500.0 | | 小肖 | 30000.0 | 263000.0 | | 张山 | 40000.0 | 263000.0 | | 李四 | 50000.0 | 263000.0 | | 小玉 | 58000.0 | 263000.0 | | 小张 | 50000.0 | 263000.0 | | 小肖 | 35000.0 | 263000.0 | | 小东 | 40000.0 | 232000.0 | | 小肖 | 35000.0 | 232000.0 | | 王武 | 56000.0 | 232000.0 | | 猪小屁 | 56000.0 | 232000.0 | | 小胡 | 25000.0 | 232000.0 | | 小林 | 20000.0 | 232000.0 | | 小非 | 23456.0 | 23456.0 | | 晓飞 | 15000.0 | 15000.0 | +-----------+---------+---------------------------------------------------------+ 16 rows in set (0.00 sec)# 注意查看dept_id = 1窗口中的name = "小肖"的值mysql> SELECT -> name,salary, -> SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) -> FROM employee2;+-----------+---------+------------------------------------------------------+| name | salary | SUM(salary) OVER(PARTITION BY dept_id ORDER BY name) |+-----------+---------+------------------------------------------------------+| 小五 | 4500.0 | 4500.0 || 小林 | 24000.0 | 28500.0 || 小张 | 50000.0 | 50000.0 || 小玉 | 58000.0 | 108000.0 || 小肖 | 30000.0 | 173000.0 || 小肖 | 35000.0 | 173000.0 || 张山 | 40000.0 | 213000.0 || 李四 | 50000.0 | 263000.0 || 小东 | 40000.0 | 40000.0 || 小林 | 20000.0 | 60000.0 || 小肖 | 35000.0 | 95000.0 || 小胡 | 25000.0 | 120000.0 || 猪小屁 | 56000.0 | 176000.0 || 王武 | 56000.0 | 232000.0 || 小非 | 23456.0 | 23456.0 || 晓飞 | 15000.0 | 15000.0 |+-----------+---------+------------------------------------------------------+16 rows in set (0.00 sec) |
而窗口函数可以和SUM()\AVG()\COUNT()\MAX()\MIN()这几个函数一起使用:
其中这些函数有一些特点，如果AVG()\COUNT()\MAX()\MIN()的括号中必须要有参数，用于统计某一列的对应的值，并且这一列中如果含有值为NULL的行，那么就会忽略值NULL的行，而COUNT()则比较特殊，如果是COUNT(*),那么就不会忽略NULL值的行，用来统计这个表中有多少行，否则，如果是COUNT(column)，统计某一列column有多少行，那么就会忽略NULL的行。
如果需要指定AVG()等小数的输出格式，则需要使用下面几个函数:
-
FORMAT(xxx,yyy,zzz)指定xxx有yyy个小数。但是这个函数有个特点，就是整数部分每三个数字就会用分隔符隔开（从小数点左边第一个数开始算的），如果不写zzz这个参数，即只有两个参数，就会以,作为分隔符了。
例如45000,如果利用FORMAT(45000，2)，最后得到的是45,000.00的形式，再例如FORMAT(45000,4)，得到的是45,000.0000
|
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
|
# 利用FORMAT,从而指定小数最后保留多少个小数点，同时从小数点左边第一个数字往左算，每三个数字# 就会有一个分隔符.注意的是，原本FORMAT()有三个参数，如果不写zzz这个参数，就会默认用','作# 为分隔符mysql> SELECT -> name, -> FORMAT(salary,4) -> FROM employee2;+-----------+------------------+| name | FORMAT(salary,4) |+-----------+------------------+| 小肖 | 30,000.0000 || 小东 | 40,000.0000 || 小非 | 23,456.0000 || 晓飞 | 15,000.0000 || 小林 | 24,000.0000 || 小五 | 4,500.0000 || 张山 | 40,000.0000 || 小肖 | 35,000.0000 || 李四 | 50,000.0000 || 王武 | 56,000.0000 || 猪小屁 | 56,000.0000 || 小玉 | 58,000.0000 || 小张 | 50,000.0000 || 小胡 | 25,000.0000 || 小肖 | 35,000.0000 || 小林 | 20,000.0000 |+-----------+------------------+16 rows in set (0.00 sec) |
-
CAST(xxx AS decimal(12,yyy)):指定xxx有yyy个小数.作用和CONVERT()一样，指定xxx有yyy个小数，但是和FORMAT()不同，他并不会每3个数字就用逗号隔开，例如45000,指定输出3个小数,则CONVERT(45000,DECIMAL(12,3)),将会输出45000.0，并没有逗号隔开.
|
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
|
mysql> SELECT -> name, -> CAST(salary AS DECIMAL(12,3)) -- 使用CAST，这时候相当于CONVERT一样，指定有多少个小数，并且不会出现分隔符 -> FROM employee2;+-----------+-------------------------------+| name | CAST(salary AS DECIMAL(12,3)) |+-----------+-------------------------------+| 小肖 | 30000.000 || 小东 | 40000.000 || 小非 | 23456.000 || 晓飞 | 15000.000 || 小林 | 24000.000 || 小五 | 4500.000 || 张山 | 40000.000 || 小肖 | 35000.000 || 李四 | 50000.000 || 王武 | 56000.000 || 猪小屁 | 56000.000 || 小玉 | 58000.000 || 小张 | 50000.000 || 小胡 | 25000.000 || 小肖 | 35000.000 || 小林 | 20000.000 |+-----------+-------------------------------+16 rows in set (0.00 sec) |
-
CONVERT(xxx,DECIMAL(12,yyy)):指定xxx有yyy个小数，但是和FORMAT()不同，他并不会每3个数字就用逗号隔开，例如45000,指定输出3个小数,则CONVERT(45000,DECIMAL(12,3)),将会输出45000.0，并没有逗号隔开.
|
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
|
# 利用CONVERT，在指定有多少个小数的同时，不会出现逗号这样的分隔符，即从小数点左边的第一个数# 字开始算，每三个数字并不会向FORMAT一样出现分隔符mysql> SELECT -> name, -> CONVERT(salary,DECIMAL(12,3)) -> FROM employee2;+-----------+-------------------------------+| name | CONVERT(salary,DECIMAL(12,3)) |+-----------+-------------------------------+| 小肖 | 30000.000 || 小东 | 40000.000 || 小非 | 23456.000 || 晓飞 | 15000.000 || 小林 | 24000.000 || 小五 | 4500.000 || 张山 | 40000.000 || 小肖 | 35000.000 || 李四 | 50000.000 || 王武 | 56000.000 || 猪小屁 | 56000.000 || 小玉 | 58000.000 || 小张 | 50000.000 || 小胡 | 25000.000 || 小肖 | 35000.000 || 小林 | 20000.000 |+-----------+-------------------------------+16 rows in set (0.00 sec) |
此外，上面三个函数除了分隔符区别外，还有的是在ORDER BY方面，因为FORMAT得到的是一个字符串，所以利用ORDER BY 的时候，此时是基于字典顺序进行排序的，而CONVERT\CAST得到的是一个数字，所以利用ORDER BY 的时候，依旧是按照数字进行排序的。
|
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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
|
# 利用CAST，然后利用这个列进行排序输出，由于CAST得到的是一个数字，所以利用ORDER BY# 的时候，就是按照数字大小进行排序的mysql> SELECT -> name, -> CAST(salary AS DECIMAL(12,3)) AS cast_salary -> FROM employee2 -> ORDER BY cast_salary; +-----------+-------------+| name | cast_salary |+-----------+-------------+| 小五 | 4500.000 || 晓飞 | 15000.000 || 小林 | 20000.000 || 小非 | 23456.000 || 小林 | 24000.000 || 小胡 | 25000.000 || 小肖 | 30000.000 || 小肖 | 35000.000 || 小肖 | 35000.000 || 小东 | 40000.000 || 张山 | 40000.000 || 李四 | 50000.000 || 小张 | 50000.000 || 王武 | 56000.000 || 猪小屁 | 56000.000 || 小玉 | 58000.000 |+-----------+-------------+16 rows in set (0.00 sec)# 利用FORMAT，然后利用这个列进行排序输出，由于FORMAT得到的是一个字符串，所以利用ORDER BY# 的时候，就是按照字典顺序进行排序的mysql> SELECT -> name, -> FORMAT(salary,3) AS format_salary -> FROM employee2 -> ORDER BY format_salary;+-----------+---------------+| name | format_salary |+-----------+---------------+| 晓飞 | 15,000.000 || 小林 | 20,000.000 || 小非 | 23,456.000 || 小林 | 24,000.000 || 小胡 | 25,000.000 || 小肖 | 30,000.000 || 小肖 | 35,000.000 || 小肖 | 35,000.000 || 小五 | 4,500.000 || 小东 | 40,000.000 || 张山 | 40,000.000 || 李四 | 50,000.000 || 小张 | 50,000.000 || 王武 | 56,000.000 || 猪小屁 | 56,000.000 || 小玉 | 58,000.000 |+-----------+---------------+16 rows in set (0.00 sec)# 利用CONVERT，然后利用这个列进行排序输出，由于CONVERT得到的是一个数字，所以利用ORDER BY# 的时候，就是按照数字大小进行排序的mysql> SELECT -> name, -> CONVERT(salary,DECIMAL(12,3)) AS convert_salary -> FROM employee2 -> ORDER BY convert_salary;+-----------+----------------+| name | convert_salary |+-----------+----------------+| 小五 | 4500.000 || 晓飞 | 15000.000 || 小林 | 20000.000 || 小非 | 23456.000 || 小林 | 24000.000 || 小胡 | 25000.000 || 小肖 | 30000.000 || 小肖 | 35000.000 || 小肖 | 35000.000 || 小东 | 40000.000 || 张山 | 40000.000 || 李四 | 50000.000 || 小张 | 50000.000 || 王武 | 56000.000 || 猪小屁 | 56000.000 || 小玉 | 58000.000 |+-----------+----------------+16 rows in set (0.00 sec) |
这一题中就有讲到输出的格式:考试分数(一)
值得一提的是，MAX()\MIN()不仅可以求解数值和日期的最值，同时可以求解文本的最值。
这里主要讲一下SUM()和窗口函数使用：SUM(xxx) OVER(PARTITION BY yyy ORDER BY zzz) :这个是根据yyy进行分组，从而划分成为了多个窗口，这些窗口根据zzz进行排序，然后每个窗口将进行连续累计xxx。
下面这一题就是运用到了SUM（)函数与窗口函数OVER()一起使用了：
统计salary的累计和running_total
最差是第几名
窗口函数还可以和排序函数一起使用
-
ROW_NUMBER() OVER()：直接表示第几行了，并不会出现并列的情况 -
DENSE_RANK() OVER()：并列连续 -
RANK() OVER()：并列不连续
|
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
71
72
73
74
75
76
77
78
79
80
81
82
83
|
# ROW_NUMBER() OVER() 直接表示第几行mysql> SELECT -> name, -> salary, -> ROW_NUMBER() OVER(ORDER BY salary DESC) -> FROM employee2;+-----------+---------+-----------------------------------------+| name | salary | ROW_NUMBER() OVER(ORDER BY salary DESC) |+-----------+---------+-----------------------------------------+| 小玉 | 58000.0 | 1 || 王武 | 56000.0 | 2 || 猪小屁 | 56000.0 | 3 || 李四 | 50000.0 | 4 || 小张 | 50000.0 | 5 || 小东 | 40000.0 | 6 || 张山 | 40000.0 | 7 || 小肖 | 35000.0 | 8 || 小肖 | 35000.0 | 9 || 小肖 | 30000.0 | 10 || 小胡 | 25000.0 | 11 || 小林 | 24000.0 | 12 || 小非 | 23456.0 | 13 || 小林 | 20000.0 | 14 || 晓飞 | 15000.0 | 15 || 小五 | 4500.0 | 16 |+-----------+---------+-----------------------------------------+16 rows in set (0.00 sec)# RANK() OVER() 表示并列，但是不会连续mysql> SELECT -> name, -> salary, -> RANK() OVER(ORDER BY salary DESC) -- 根据salary降序进行排序 -> FROM employee2;+-----------+---------+-----------------------------------+| name | salary | RANK() OVER(ORDER BY salary DESC) |+-----------+---------+-----------------------------------+| 小玉 | 58000.0 | 1 || 王武 | 56000.0 | 2 | -- --| 这两组同处于第2，但是不会连续，所以下一组是| 猪小屁 | 56000.0 | 2 | -- --| 从4开始了| 李四 | 50000.0 | 4 || 小张 | 50000.0 | 4 || 小东 | 40000.0 | 6 || 张山 | 40000.0 | 6 || 小肖 | 35000.0 | 8 || 小肖 | 35000.0 | 8 || 小肖 | 30000.0 | 10 || 小胡 | 25000.0 | 11 || 小林 | 24000.0 | 12 || 小非 | 23456.0 | 13 || 小林 | 20000.0 | 14 || 晓飞 | 15000.0 | 15 || 小五 | 4500.0 | 16 |+-----------+---------+-----------------------------------+16 rows in set (0.00 sec)# DENSE_RANK() OVER() 并列连续排序mysql> SELECT -> name, -> salary, -> DENSE_RANK() OVER(ORDER BY salary DESC) -> FROM employee2;+-----------+---------+-----------------------------------------+| name | salary | DENSE_RANK() OVER(ORDER BY salary DESC) |+-----------+---------+-----------------------------------------+| 小玉 | 58000.0 | 1 || 王武 | 56000.0 | 2 | -- |这两组并列第2，并且是连续排序的| 猪小屁 | 56000.0 | 2 | -- |所以下一组是从3开始的| 李四 | 50000.0 | 3 || 小张 | 50000.0 | 3 || 小东 | 40000.0 | 4 || 张山 | 40000.0 | 4 || 小肖 | 35000.0 | 5 || 小肖 | 35000.0 | 5 || 小肖 | 30000.0 | 6 || 小胡 | 25000.0 | 7 || 小林 | 24000.0 | 8 || 小非 | 23456.0 | 9 || 小林 | 20000.0 | 10 || 晓飞 | 15000.0 | 11 || 小五 | 4500.0 | 12 |+-----------+---------+-----------------------------------------+16 rows in set (0.00 sec) |
此外窗口函数还可以和其他一些函数使用，这里就不列举了。
利用了排序函数对应的练习:刷题通过的题目排名
参考资料：
WHAT IS the MySQL OVER clause？
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/weixin_46544385/article/details/120609601
