<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
MySQL視窗函數OVER()用法及說明
2022-08-16 18:05:18
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 ASCDESC)
每個視窗中利用ORDER BY子句，這時候將按照yyy1進行對應的升序降序的順序進行排序,如果yyy1相同，將根據yyy2排序（和ORDER BY 的用法一樣)，這時候不僅會進行排序操作，如果是SUM與其連用的話，同時進行了累加的操作，即值是當前行加上前一行對應的值。但是下面的例子中卻發現ORDER BY 後面對應的值相同的時候，並不是當前這一行加上以前行的值，例如ORDER BY salaryORDER 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 ASCDESC)
根據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 的時候，此時是基於字典順序進行排序的，而CONVERTCAST得到的是一個數位，所以利用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？
以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。
相關文章
-
黑科技的輕簡出行，告別「電量焦慮症」：Anker氮化鎵超能充系列
综合看Anker超能充系列的性价比很高，并且与不仅和iPhone12/苹果<em>Mac</em>Book很配，而且适合多设备充电需求的日常使用或差旅场景，不管是安卓还是Switch同样也能用得上它，希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
-
吳亦凡廠牌首秀，L4WUDU居然忘詞了，Rapper和明星比還是有差距！
除了L4WUDU与吴亦凡已经多次共事，成为了明面上的厂牌成员，吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节，这也是20XXCLUB首次全员合照，王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
-
IPFS、Chia、Bzz和ICP挖礦該怎麼選？哪一個更有優勢？
目前应用IPFS的机构：1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆，历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
-
有哪些事是買了雪佛蘭後才知道的？美事偷著樂，開拓者車主隨聊
开拓者的车机是兼容苹果和<em>安卓</em>，虽然我不怎么用，但确实兼顾了我家人的很多需求：副驾的门板还配有解锁开关，有的时候老婆开车，下车的时候偶尔会忘记解锁，我在副驾驶可以自己开门：第二排设计很好，不仅配置了一个很大的
2021-06-01 09:30:48
-
iPhone12在618最新定價，跌價幅度超過1400元，還等iPhone13嗎？
不仅是<em>安卓</em>手机，苹果手机的降价力度也是前所未有了，iPhone12也“跳水价”了，发布价是6799元，如今已经跌至5308元，降价幅度超过1400元，最新定价确认了。iPhone12是苹果首款5G手机，同时也是全球首款5nm芯片的智能机，它
2021-06-01 09:30:45