欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL窗口函數(shù)OVER()用法及說明

 更新時(shí)間:2022年08月16日 14:33:28   作者:奮斗的龍貓  
這篇文章主要介紹了MySQL窗口函數(shù)OVER()用法及說明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

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è)參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • 詳解關(guān)于MySQL 8.0走過的坑

    詳解關(guān)于MySQL 8.0走過的坑

    這篇文章主要介紹了詳解關(guān)于MySQL 8.0走過的坑,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧
    2018-09-09
  • 為MySQL安裝配置代理工具Kingshard的基本教程

    為MySQL安裝配置代理工具Kingshard的基本教程

    這篇文章主要介紹了為MySQL安裝配置代理工具Kingshard的基本教程,Kingshard由Go語言寫成,可以實(shí)現(xiàn)讀寫分離和客戶端IP訪問控制等功能,非常強(qiáng)大,需要的朋友可以參考下
    2015-12-12
  • MySQL無法啟動(dòng)幾種常見問題小結(jié)

    MySQL無法啟動(dòng)幾種常見問題小結(jié)

    在群里看到有新同學(xué)還在問MySQL無法啟動(dòng)的問題,于是總結(jié)了幾個(gè)常見情況與解決方法,需要的朋友可以參考下
    2013-09-09
  • CentOS 7中源碼安裝MySQL 5.7.6+詳細(xì)教程

    CentOS 7中源碼安裝MySQL 5.7.6+詳細(xì)教程

    最近在CentOS 7中源碼安裝MySQL 5.7.6+,發(fā)現(xiàn)MySQL5.7.6+以后的安裝方式真的與以前版本的MySQL安裝方式大大的不同呀。不自己安裝一把,你都不知道不同之處在哪,下面這篇文章是通過自己的安裝過程總結(jié)的一篇安裝教程,有需要的朋友們可以參考借鑒,下面來一起看看吧。
    2016-12-12
  • mysql的左右內(nèi)連接用法實(shí)例

    mysql的左右內(nèi)連接用法實(shí)例

    這篇文章主要介紹了mysql的左右內(nèi)連接用法,以一個(gè)完整實(shí)例較為詳細(xì)的分析了mysql的左右內(nèi)連接使用技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下
    2015-02-02
  • 詳解mysql持久化統(tǒng)計(jì)信息

    詳解mysql持久化統(tǒng)計(jì)信息

    這篇文章主要介紹了mysql持久化統(tǒng)計(jì)信息的相關(guān)資料,幫助大家更好的理解和使用MySQL數(shù)據(jù)庫,感興趣的朋友可以了解下
    2020-12-12
  • MySQL生成日期維度表的sql語句

    MySQL生成日期維度表的sql語句

    這篇文章主要介紹了MySQL生成日期維度表的sql語句,通過存儲(chǔ)過程生成,其次是通過遞歸的公用表表達(dá)式生成,需要的朋友可以參考下
    2024-07-07
  • Mysql如何巧妙的繞過未知字段名詳解

    Mysql如何巧妙的繞過未知字段名詳解

    這篇文章主要給大家介紹了Mysql如何巧妙的繞過未知字段名的相關(guān)資料,文中給出了詳細(xì)的示例代碼供大家參考學(xué)習(xí),對(duì)學(xué)習(xí)mysql具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起看看吧。
    2017-05-05
  • 使用mysql中遇到的幾個(gè)問題

    使用mysql中遇到的幾個(gè)問題

    首先mysql不是可視化的,可以通過命令行進(jìn)行操作,包括創(chuàng)建數(shù)據(jù)庫、表、添加數(shù)據(jù)等等。那豈不是很不方便了嗎?
    2009-07-07
  • mysql關(guān)聯(lián)兩張表時(shí)的編碼問題及解決辦法

    mysql關(guān)聯(lián)兩張表時(shí)的編碼問題及解決辦法

    在本篇文章里小編給大家整理的是關(guān)于mysql關(guān)聯(lián)兩張表時(shí)的編碼問題及解決辦法,有需要的朋友們可以參考下。
    2019-09-09

最新評(píng)論