MySQL窗口函數(shù)OVER()用法及說明
MySQL窗口函數(shù)OVER()
下面的講解將基于這個(gè)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)
窗口函數(shù)是OVER(),其中對(duì)應(yīng)子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
OVER()
這時(shí)候,是一個(gè)空子句,此時(shí)的效果和沒有使用OVER()函數(shù)是一樣的,作用的是這個(gè)表所有數(shù)據(jù)構(gòu)成的窗口。
mysql> SELECT -> name, -> salary, -> MAX(salary) OVER() AS max_salary -- 作用于一整個(gè)窗口,此時(shí)返回的是所有數(shù)據(jù)中的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以及這個(gè)部門的最大工資 -> 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 子句,此時(shí)就會(huì)根據(jù)yyy1,yyy2,yyy3這些列構(gòu)成的整體進(jìn)行劃分窗口,只有這些列構(gòu)成的整體相同,才會(huì)處在同一個(gè)窗口中。
mysql> SELECT -> name, -> salary, -> MAX(salary) OVER(PARTITION BY dept_id) AS dept_max_salary -- 利用了PARTITION BY ,從而根據(jù)dept_id進(jìn)行分組,然后獲取每個(gè)分組的最大值 -> 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)
每個(gè)窗口中利用ORDER BY子句,這時(shí)候?qū)凑誽yy1進(jìn)行對(duì)應(yīng)的升序\降序的順序進(jìn)行排序,如果yyy1相同,將根據(jù)yyy2排序(和ORDER BY 的用法一樣),這時(shí)候不僅會(huì)進(jìn)行排序操作,如果是SUM與其連用的話,同時(shí)進(jìn)行了累加的操作,即值是當(dāng)前行加上前一行對(duì)應(yīng)的值。但是下面的例子中卻發(fā)現(xiàn)ORDER BY 后面對(duì)應(yīng)的值相同的時(shí)候,并不是當(dāng)前這一行加上以前行的值,例如ORDER BY salary\ORDER BY name的時(shí)候。
mysql> SELECT -> name, -> salary, -> SUM(salary) OVER(ORDER BY salary) AS already_paid_salary -- 利用ORDER BY ,窗口中對(duì)應(yīng)的行將按照salary進(jìn)行升序排序,然后調(diào)用SUM聚集 函數(shù),不同的窗口進(jìn)行累計(jì) -> 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 | -- -----| 這兩行同處相同,此時(shí)這個(gè)窗口的already_paid_salary | 小肖 | 35000.0 | 211956.0 | -- -----| = (35000 * 2) (當(dāng)前兩行) + 141956(前面的行) | 小東 | 40000.0 | 291956.0 | -- ---| 這兩行同處相同,此時(shí)這個(gè)窗口的already_paid_salary | 張山 | 40000.0 | 291956.0 | -- ---| = (40000 * 2)(當(dāng)前兩行) + 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) -- 每個(gè)窗口的所有行將根據(jù)name進(jìn)行升序排序這時(shí)候,然后不同name的行將會(huì)進(jìn)行累計(jì)操作,直接是當(dāng)前行+以嵌行的,相同的時(shí)候,是相同行的和加上之前行的值 -> 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 | -- |這兩組同處相同,所以對(duì)應(yīng)的值為(24000 + 20000)(相同的兩行) + 94500(之前的行) | 小林 | 20000.0 | 138500.0 | -- | | 小玉 | 58000.0 | 196500.0 | | 小肖 | 30000.0 | 296500.0 | -- ---|這兩組同處相同,所以對(duì)應(yīng)的值為(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)
同時(shí)值得注意的是,OVER()是一個(gè)全局函數(shù),所以在使用ORDER BY 的時(shí)候,那么最后輸出的時(shí)候也將是按照這個(gè)有序輸出,但是僅僅在沒有使用PARTITION BY的情況才是這樣的.這個(gè)可以從PARTITION BY進(jìn)行說明,沒有使用PARTITION BY的時(shí)候,ORVER()中的ORDER BY將是針對(duì)整張表進(jìn)行排序的,所以這時(shí)候如果FROM子句后面的ORDER BY后的字段和OVER()中ORDER BY后的字段相同的時(shí)候,就會(huì)顯得多此一舉了。
即
# 下面兩個(gè)代碼是一樣的,但是僅僅OVER()只使用ORDER BY子句的時(shí)候,并且才這樣 # 兩個(gè)ORDER BY后面的字段是相同才可以保證效果一樣 # 如果使用了PARTITION BY子句,那么OVER()中的ORDER BY將是針對(duì)每一個(gè)窗口 # 中的所有行進(jìn)行排序的,而在FROM子句后面的ORDER BY將是針對(duì)整張表,所以 # 導(dǎo)致結(jié)果不同 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) # 兩個(gè)ORDER BY后面的字段相同時(shí),作用就會(huì)相當(dāng)只使用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) # 兩個(gè)ORDER BY后的字段不同,那么FROM 子句后的ORDER BY將會(huì)覆蓋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針對(duì)的窗口中的所有行進(jìn)行排序的,而下面的FROM子句中的 # ORDER BY是針對(duì)整個(gè)表的,所以此時(shí)兩者的作用并不相同 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)
根據(jù)PARTITION BY ,此時(shí)表示根據(jù)yyy進(jìn)行分組,然后在每個(gè)窗口中的所有行將利用ORDER BY 子句,將根據(jù)zzz進(jìn)行排序。值得注意的是,如果zzz和yyy相同的時(shí)候,這時(shí)候作用相當(dāng)于OVER(PARTITION BY yyy),和沒有ORDER BY子句是一樣的,因?yàn)槎继幵谝粋€(gè)窗口了。
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)
而窗口函數(shù)可以和SUM()\AVG()\COUNT()\MAX()\MIN()這幾個(gè)函數(shù)一起使用:
其中這些函數(shù)有一些特點(diǎn),如果AVG()\COUNT()\MAX()\MIN()的括號(hào)中必須要有參數(shù),用于統(tǒng)計(jì)某一列的對(duì)應(yīng)的值,并且這一列中如果含有值為NULL的行,那么就會(huì)忽略值NULL的行,而COUNT()則比較特殊,如果是COUNT(*),那么就不會(huì)忽略NULL值的行,用來統(tǒng)計(jì)這個(gè)表中有多少行,否則,如果是COUNT(column),統(tǒng)計(jì)某一列column有多少行,那么就會(huì)忽略NULL的行。
如果需要指定AVG()等小數(shù)的輸出格式,則需要使用下面幾個(gè)函數(shù):
FORMAT(xxx,yyy,zzz)
指定xxx有yyy個(gè)小數(shù)。但是這個(gè)函數(shù)有個(gè)特點(diǎn),就是整數(shù)部分每三個(gè)數(shù)字就會(huì)用分隔符隔開(從小數(shù)點(diǎn)左邊第一個(gè)數(shù)開始算的),如果不寫zzz這個(gè)參數(shù),即只有兩個(gè)參數(shù),就會(huì)以,作為分隔符了。
例如45000,如果利用FORMAT(45000,2),最后得到的是45,000.00的形式,再例如FORMAT(45000,4),得到的是45,000.0000
# 利用FORMAT,從而指定小數(shù)最后保留多少個(gè)小數(shù)點(diǎn),同時(shí)從小數(shù)點(diǎn)左邊第一個(gè)數(shù)字往左算,每三個(gè)數(shù)字 # 就會(huì)有一個(gè)分隔符.注意的是,原本FORMAT()有三個(gè)參數(shù),如果不寫zzz這個(gè)參數(shù),就會(huì)默認(rèn)用','作 # 為分隔符 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個(gè)小數(shù).作用和CONVERT()一樣,指定xxx有yyy個(gè)小數(shù),但是和FORMAT()不同,他并不會(huì)每3個(gè)數(shù)字就用逗號(hào)隔開,例如45000,指定輸出3個(gè)小數(shù),則CONVERT(45000,DECIMAL(12,3)),將會(huì)輸出45000.0,并沒有逗號(hào)隔開.
mysql> SELECT -> name, -> CAST(salary AS DECIMAL(12,3)) -- 使用CAST,這時(shí)候相當(dāng)于CONVERT一樣,指定有多少個(gè)小數(shù),并且不會(huì)出現(xiàn)分隔符 -> 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個(gè)小數(shù),但是和FORMAT()不同,他并不會(huì)每3個(gè)數(shù)字就用逗號(hào)隔開,例如45000,指定輸出3個(gè)小數(shù),則CONVERT(45000,DECIMAL(12,3)),將會(huì)輸出45000.0,并沒有逗號(hào)隔開.
# 利用CONVERT,在指定有多少個(gè)小數(shù)的同時(shí),不會(huì)出現(xiàn)逗號(hào)這樣的分隔符,即從小數(shù)點(diǎn)左邊的第一個(gè)數(shù) # 字開始算,每三個(gè)數(shù)字并不會(huì)向FORMAT一樣出現(xiàn)分隔符 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)
此外,上面三個(gè)函數(shù)除了分隔符區(qū)別外,還有的是在ORDER BY方面,因?yàn)镕ORMAT得到的是一個(gè)字符串,所以利用ORDER BY 的時(shí)候,此時(shí)是基于字典順序進(jìn)行排序的,而CONVERT\CAST得到的是一個(gè)數(shù)字,所以利用ORDER BY 的時(shí)候,依舊是按照數(shù)字進(jìn)行排序的。
# 利用CAST,然后利用這個(gè)列進(jìn)行排序輸出,由于CAST得到的是一個(gè)數(shù)字,所以利用ORDER BY # 的時(shí)候,就是按照數(shù)字大小進(jìn)行排序的 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,然后利用這個(gè)列進(jìn)行排序輸出,由于FORMAT得到的是一個(gè)字符串,所以利用ORDER BY # 的時(shí)候,就是按照字典順序進(jìn)行排序的 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,然后利用這個(gè)列進(jìn)行排序輸出,由于CONVERT得到的是一個(gè)數(shù)字,所以利用ORDER BY # 的時(shí)候,就是按照數(shù)字大小進(jìn)行排序的 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)
這一題中就有講到輸出的格式:考試分?jǐn)?shù)(一)
值得一提的是,MAX()\MIN()不僅可以求解數(shù)值和日期的最值,同時(shí)可以求解文本的最值。
這里主要講一下SUM()和窗口函數(shù)使用:SUM(xxx) OVER(PARTITION BY yyy ORDER BY zzz) :這個(gè)是根據(jù)yyy進(jìn)行分組,從而劃分成為了多個(gè)窗口,這些窗口根據(jù)zzz進(jìn)行排序,然后每個(gè)窗口將進(jìn)行連續(xù)累計(jì)xxx。
下面這一題就是運(yùn)用到了SUM()函數(shù)與窗口函數(shù)OVER()一起使用了:
統(tǒng)計(jì)salary的累計(jì)和running_total
窗口函數(shù)還可以和排序函數(shù)一起使用
ROW_NUMBER() OVER()
:直接表示第幾行了,并不會(huì)出現(xiàn)并列的情況DENSE_RANK() OVER()
:并列連續(xù)RANK() OVER()
:并列不連續(xù)
# 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() 表示并列,但是不會(huì)連續(xù) mysql> SELECT -> name, -> salary, -> RANK() OVER(ORDER BY salary DESC) -- 根據(jù)salary降序進(jìn)行排序 -> FROM employee2; +-----------+---------+-----------------------------------+ | name | salary | RANK() OVER(ORDER BY salary DESC) | +-----------+---------+-----------------------------------+ | 小玉 | 58000.0 | 1 | | 王武 | 56000.0 | 2 | -- --| 這兩組同處于第2,但是不會(huì)連續(xù),所以下一組是 | 豬小屁 | 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() 并列連續(xù)排序 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,并且是連續(xù)排序的 | 豬小屁 | 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)
此外窗口函數(shù)還可以和其他一些函數(shù)使用,這里就不列舉了。
利用了排序函數(shù)對(duì)應(yīng)的練習(xí):刷題通過的題目排名
參考資料:
WHAT IS the MySQL OVER clause?
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
- SQL窗口函數(shù)之partition by的使用
- MySQL窗口函數(shù) over(partition by)的用法
- MySQL8.0實(shí)現(xiàn)窗口函數(shù)計(jì)算同比環(huán)比
- SQL中去除重復(fù)數(shù)據(jù)的幾種方法匯總(窗口函數(shù)對(duì)數(shù)據(jù)去重)
- SQL實(shí)現(xiàn)篩選出連續(xù)3天登錄用戶與窗口函數(shù)的示例代碼
- SQL窗口函數(shù)之聚合窗口函數(shù)的使用(count,max,min,sum)
- MySQL8.0窗口函數(shù)入門實(shí)踐及總結(jié)
- SQL中的窗口函數(shù)簡介
相關(guān)文章
CentOS 7中源碼安裝MySQL 5.7.6+詳細(xì)教程
最近在CentOS 7中源碼安裝MySQL 5.7.6+,發(fā)現(xiàn)MySQL5.7.6+以后的安裝方式真的與以前版本的MySQL安裝方式大大的不同呀。不自己安裝一把,你都不知道不同之處在哪,下面這篇文章是通過自己的安裝過程總結(jié)的一篇安裝教程,有需要的朋友們可以參考借鑒,下面來一起看看吧。2016-12-12mysql關(guān)聯(lián)兩張表時(shí)的編碼問題及解決辦法
在本篇文章里小編給大家整理的是關(guān)于mysql關(guān)聯(lián)兩張表時(shí)的編碼問題及解決辦法,有需要的朋友們可以參考下。2019-09-09