MySQL窗口函数OVER()用法及说明
目录
- MySQL窗口函数OVER()
- 下面的讲解将基于这个employee2表
MySQL窗口函数OVER()
下面的讲解将基于这个employee2表
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()函数是一样的，作用的是这个表所有数据构成的窗口。
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这些列构成的整体进行划分窗口，只有这些列构成的整体相同，才会处在同一个窗口中。
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的时候。
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后的字段相同的时候，就会显得多此一举了。
即
# 下面两个代码是一样的，但是仅仅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 employee2
ORDER 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 BY
mysql> 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子句是一样的，因为都处在一个窗口了。
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
# 利用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，并没有逗号隔开.
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，并没有逗号隔开.
# 利用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 的时候，依旧是按照数字进行排序的。
# 利用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()一起使用了：
窗口函数还可以和排序函数一起使用
ROW_NUMBER() OVER()：直接表示第几行了，并不会出现并列的情况DENSE_RANK() OVER()：并列连续RANK() OVER()：并列不连续
# 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？
以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。
赞 (0)
相关推荐
-
MySQL数据库基础篇SQL窗口函数示例解析教程
目录 本文简介 正文介绍 聚合函数 + over() 排序函数 + over() ntile()函数 + over() 偏移函数 + over() 本文简介 前段时间,黄同学写了一篇<MySQL窗口实战>文章(文章如下),但是里面大多数是以实战练习为主,没有做详细的解释. 传送门:MySQL实战窗口函数SQL分析班级学生考试成绩及生活消费 于是,私信了月牙美女,看看她能否写一篇<窗口函数基础篇>,正好和之前那篇文章配套.这不,很快她就写好了,今天就给大家做一个分享,旨在和大家交流学
-
MySQL8.0中的窗口函数的示例代码
目录 1.窗口函数与聚合函数 2.常见的窗口函数 3.over子句 4.代码示例 4.1row_number\dense_rank\ rank 4.2cume_dist\percent_rank 4.3first_value\last_value\nth_value 4.4ntile() 4.5lag\lead 4.6聚合函数 4.7orderby子句 4.8 window子句 4.9 rows和range 在以前的MySQL版本中是没有窗口函数的,直到MySQL8.0才引入了窗口函数.窗口函数
-
MySQL 开窗函数
目录 (1)开窗函数的定义 (2)开窗函数的实际应用场景 结合order by关键词和limit关键词是可以解决很多的topN问题,比如从二手房数据集中查询出某个地区的最贵的10套房,从电商交易数据集中查询出实付金额最高的5笔交易,从学员信息表中查询出年龄最小的3个学员等.但是,如果需求变成从二手房数据集中查询出各个地区最贵的10套房,从电商数据集中查询出每月实付金额最高的5笔交易,从学员信息表中查询出各个科系下年龄最小的3个学员,该如何解决呢? 其实这类问题的核心就是,筛选出组内的topN,而
-
MySQL窗口函数的具体使用
目录 一.什么是窗口函数 1.怎么理解窗口? 2.什么是窗口函数 二.窗口函数用法 1.序号函数:row_number() / rank() / dense_rank() 2.分布函数:percent_rank() / cume_dist() 3.前后函数:lag(expr,n) / lead(expr,n) 4.头尾函数:FIRST_VALUE(expr).LAST_VALUE(expr) 5.其他函数:nth_value() / nfile() 本章小结 之前我给粉丝们搞过个投票,寻找M
-
MySQL窗口函数OVER()用法及说明
目录 MySQL窗口函数OVER() 下面的讲解将基于这个employee2表 MySQL窗口函数OVER() 下面的讲解将基于这个employee2表 mysql> SELECT * FROM employee2; +----+-----------+------+---------+---------+ | id | name | age | salary | dept_id | +----+-----------+------+---------+---------+ | 3 | 小肖 |
-
php中mysql操作buffer用法详解
本文实例讲述了php中mysql操作buffer用法.分享给大家供大家参考.具体分析如下: php与mysql的连接有三种方式,mysql,mysqli,pdo.不管使用哪种方式进行连接,都有使用buffer和不使用buffer的区别. 什么叫使用buffer和不使用buffer呢? 客户端与mysql服务端进行查询操作,查询操作的时候如果获取的数据量比较大,那个这个查询结果放在哪里呢? 有两个地方可以放:客户端的缓冲区和服务端的缓冲区. 我们这里说的buffer指的是客户端的缓冲区,如果查询结
-
MySQL子查询用法实例分析
本文实例讲述了MySQL子查询用法.分享给大家供大家参考,具体如下: 假设表my_tbl包含三个字段a,b,c:现在需要查询表中列a的每个不同值下的列b为最小值的记录量. 比如表记录为: a b c 1 3 'cd' 2 3 'nhd' 1 5 'bg' 2 6 'cds' 1 7 'kiy' 3 7 'vsd' 3 8 'ndf' 希望得到结果为: a b c 1 3 'cd' 2 3 'nhd' 3 7 'vsd' (1) 其中一个做法:先查出
-
mysql group_concat()函数用法总结
本文实例讲述了mysql group_concat()函数用法.分享给大家供大家参考,具体如下: group_concat(),手册上说明:该函数返回带有来自一个组的连接的非NULL值的字符串结果.比较抽象,难以理解. 通俗点理解,其实是这样的:group_concat()会计算哪些行属于同一组,将属于同一组的列显示出来.要返回哪些列,由函数参数(就是字段名)决定.分组必须有个标准,就是根据group by指定的列进行分组. group_concat函数应该是在内部执行了group by语句,这
-
MySQL切分查询用法分析
本文实例讲述了MySQL切分查询用法.分享给大家供大家参考,具体如下: 对于大查询有时需要'分而治之',将大查询切分为小查询: 每个查询功能完全一样,但只完成原来的一小部分,每次查询只返回一小部分结果集. 删除旧的数据就是一个很好地例子.定期清理旧数据时,如果一条sql涉及了大量的数据时,可能会一次性锁住多个表或行,耗费了大量的系统资源,却阻塞了其他很多小的但重要的查询.将一个大得DELETE语句切分为较小的查询时,可以尽量减少影响msql的性能,同时减少mysql复制造成的延迟. 例如,每个月
-
php封装的连接Mysql类及用法分析
本文实例讲述了php封装的连接Mysql类及用法.分享给大家供大家参考,具体如下: class mysql{ private $db_name; private $db_host; private $db_user; private $db_pwd; private $conn; private $querysql; private $result; private $resultarray=array(); private $row; //创建构造函数 数据库名 主机名 用户名 密码 func
-
MySQL存储结构用法案例分析
本文实例讲述了MySQL存储结构用法.分享给大家供大家参考,具体如下: 前言 今天公司老大让我做一个MySQL的调研工作,是关于MySQL的存储结构的使用.这里我会通过3个例子来介绍一下MySQL中存储结构的使用过程,以及一些需要注意的点. 笔者环境 系统:Windows 7 MySQL:MySQL 5.0.96 准备工作 1.新建两张数据表:student1, student2 新建student1 DROP TABLE IF EXISTS student1; CREATE TABLE stu
-
MySQL触发器简单用法示例
本文实例讲述了MySQL触发器简单用法.分享给大家供大家参考,具体如下: mysql触发器和存储过程一样,是嵌入到mysql的一段程序,触发器是由事件来触发的,这些事件包括,INSERT,UPDATE,DELETE,不包括SELECT 创建触发器 CREATE TRIGGER name,time,event ON table_name FOR EACH ROW trigger_stmt 例如 复制代码 代码如下: CREATE TRIGGER ins_sum BEFORE INSERT ON a
-
node.js连接mysql与基本用法示例
本文实例讲述了node.js连接mysql与基本用法.分享给大家供大家参考,具体如下: 下载mysql模块 使用命令npm install mysql下载mysql模块 mysql引入模块 var mysql = require("mysql"); 创建连接池 使用createPool()创建一个mysql连接池,传入一个表参数作为连接信息 var pool = mysql.createPool({ host:"127.0.0.1", port:3306, //默认