SQL 語法 進階 GROUP BY

測試環境為 CentOS 8 x86_64 (虛擬機) ,下面針對 SQL SELECT 的 GROUP BY 使用詳細說明.

先建立一個資料庫 (testdb) , 與 tables (employee) 格式為 Name VARCHAR(20), Dept VARCHAR(20), jobTitle VARCHAR(20) 各 20 個字元以及 數字欄位 Age INT(3) , Seniority INT(3) , Salary INT(11).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (Name VARCHAR(20) , Dept VARCHAR(20) , JobTitle VARCHAR(20) ,Age INT(3) , Seniority INT(3) , Salary INT(11));
Query OK, 0 rows affected (0.009 sec)

MariaDB [testdb]> DESCRIBE employee; 
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Name      | varchar(20) | YES  |     | NULL    |       |
| Dept      | varchar(20) | YES  |     | NULL    |       |
| JobTitle  | varchar(20) | YES  |     | NULL    |       |
| Age       | int(3)      | YES  |     | NULL    |       |
| Seniority | int(3)      | YES  |     | NULL    |       |
| Salary    | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.005 sec)

透過 INSERT 新增加了五筆資料.

MariaDB [testdb]> INSERT INTO employee VALUES ('Ben' , 'Testing' , 'Engineer' , '32' , '5' , '45000') , ('Afa' , 'Power' , 'Engineer' , '28' , '2' , '48000') , ('Boss' , 'Testing' , 'Manager' , '42' , '8' , '75000') , ('Justin' , 'Testing' , 'Senior Engineer' , ' 26' , '2' , '42000') , ('Jasmine' , 'Power' , 'Manager' , '41' , '9' ,'80000') ;
Query OK, 5 rows affected (0.002 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+---------+---------+-----------------+------+-----------+--------+
| Name    | Dept    | JobTitle        | Age  | Seniority | Salary |
+---------+---------+-----------------+------+-----------+--------+
| Ben     | Testing | Engineer        |   32 |         5 |  45000 |
| Afa     | Power   | Engineer        |   28 |         2 |  48000 |
| Boss    | Testing | Manager         |   42 |         8 |  75000 |
| Justin  | Testing | Senior Engineer |   26 |         2 |  42000 |
| Jasmine | Power   | Manager         |   41 |         9 |  80000 |
+---------+---------+-----------------+------+-----------+--------+
5 rows in set (0.001 sec)

通常 SELECT 後的資料會透過 GROUP BY 來分類,下面來看一下怎麼使用.

MariaDB [testdb]> HELP SELECT;
...
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]

要來看資料裡面有哪些單位 Dep 就直接透過 GROUP BY Dept 即可.

MariaDB [testdb]> SELECT Dept FROM employee GROUP BY Dept;
+---------+
| Dept    |
+---------+
| Power   |
| Testing |
+---------+
2 rows in set (0.001 sec)

資料會以 GROUP BY 的欄位做排序,預設為 ASC (從小到大的順序,文字大小是以 ASCII 的編碼為順序),可以改為 DESC (從大到小的順序).

MariaDB [testdb]> SELECT Dept FROM employee GROUP BY Dept ASC;
+---------+
| Dept    |
+---------+
| Power   |
| Testing |
+---------+
2 rows in set (0.001 sec)
MariaDB [testdb]> SELECT Dept FROM employee GROUP BY Dept DESC;
+---------+
| Dept    |
+---------+
| Testing |
| Power   |
+---------+
2 rows in set (0.004 sec)

所有的資料都透過 Dep 欄位被分類成同一組了,那其他欄位的資料會顯示什麼呢?

MariaDB [testdb]> SELECT * FROM employee GROUP BY Dept;
+------+---------+----------+------+-----------+--------+
| Name | Dept    | JobTitle | Age  | Seniority | Salary |
+------+---------+----------+------+-----------+--------+
| Afa  | Power   | Engineer |   28 |         2 |  48000 |
| Ben  | Testing | Engineer |   32 |         5 |  45000 |
+------+---------+----------+------+-----------+--------+
2 rows in set (0.001 sec)

這些資料可能不是我們想要的,這時候可以透過函數 grouping function (Aggregate Functions ,所有可使用函數請參考 – https://mariadb.com/kb/en/aggregate-functions/) 來篩選出你要保留的資料,下面幾個範例.

  • SUM
    如果是數字欄位如 Salary 就可以透過 SUM 函數把 GROUP BY 分類的所有薪水加總.

    MariaDB [testdb]>  SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | Power   | 128000 |
    | Testing | 162000 |
    +---------+--------+
    2 rows in set (0.001 sec)
    

    WITH ROLLUP 可以把分類的薪水資料做個總結.

    MariaDB [testdb]> SELECT Dept, SUM(salary) AS Total FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+--------+
    | Dept    | Total  |
    +---------+--------+
    | Power   | 128000 |
    | Testing | 162000 |
    | NULL    | 290000 |
    +---------+--------+
    3 rows in set (0.001 sec)
    

    透過函數 IFNULL 來把總合 ROLLUP 欄位顯示為 NULL 取代成我們想要的字串.

    MariaDB [testdb]> SELECT IFNULL(Dept,'Total') AS DEPT, SUM(Salary) FROM employee GROUP BY Dept WITH ROLLUP;
    +---------+-------------+
    | DEPT    | SUM(Salary) |
    +---------+-------------+
    | Power   |      128000 |
    | Testing |      162000 |
    | Total   |      290000 |
    +---------+-------------+
    3 rows in set, 1 warning (0.001 sec)
    

    函數說明:

    • IFNULL : 用來判斷 Expression 字串(第一個參數)是否為 NULL ,是就以 ALTER_Value (第二個參數)來取代.

    有辨法能直接多一的欄位來顯示該單位的薪水總數嗎? 可以透過 OVER 的方式來處理.

    MariaDB [testdb]> SELECT Name , Dept , JobTitle , Salary, SUM(Salary) OVER (PARTITION BY Dept) AS Dept_Total FROM employee;
    +---------+---------+-----------------+--------+------------+
    | Name    | Dept    | JobTitle        | Salary | Dept_Total |
    +---------+---------+-----------------+--------+------------+
    | Afa     | Power   | Engineer        |  48000 |     128000 |
    | Jasmine | Power   | Manager         |  80000 |     128000 |
    | Ben     | Testing | Engineer        |  45000 |     162000 |
    | Justin  | Testing | Senior Engineer |  42000 |     162000 |
    | Boss    | Testing | Manager         |  75000 |     162000 |
    +---------+---------+-----------------+--------+------------+
    5 rows in set (0.002 sec)
    

    來看一下 OVER PARTITION BY 的使用方式,詳細使用方式請參考 – http://benjr.tw/102501 .

    SUM(Salary) OVER (PARTITION BY Dept) 
    
    • PARTITION BY Dept – 會依據 Dept 欄位來分類.
    • SUM(Salary) OVER – 會依據 PARTITION BY Dept 分類的結果來加總 (SUM).
    • AS Dept_Total – 欄位就會顯示依據各單位 Dept 欄位的加總
  • MAX(最大) 或是 MIN(最小)
    如果是數字欄位如 Salary 就可以透過 MAX(最大) 或是 MIN(最小) 函數來顯示最高或是最新薪水.

    MariaDB [testdb]> SELECT Dept, MAX(Salary) AS "Maximum salary" FROM employee GROUP BY Dept;
    +---------+----------------+
    | Dept    | Maximum salary |
    +---------+----------------+
    | Power   |          80000 |
    | Testing |          75000 |
    +---------+----------------+
    2 rows in set (0.001 sec)
    
    MariaDB [testdb]> SELECT Dept, MIN(Salary) AS "Minimum Salary" FROM employee GROUP BY Dept;
    +---------+----------------+
    | Dept    | Minimum Salary |
    +---------+----------------+
    | Power   |          48000 |
    | Testing |          42000 |
    +---------+----------------+
    2 rows in set (0.001 sec)
    
  • AVG
    如果是數字欄位如 Salary 就可以透過 AVG 計算平均薪水.

    MariaDB [testdb]> SELECT Dept, AVG(Salary) AS "Average salary" FROM employee GROUP BY Dept;
    +---------+----------------+
    | Dept    | Average salary |
    +---------+----------------+
    | Power   |     64000.0000 |
    | Testing |     54000.0000 |
    +---------+----------------+
    2 rows in set (0.002 sec)
    
  • COUNT
    透過 COUNT 函數,可以統計資料有幾筆.

    MariaDB [testdb]> SELECT Dept, COUNT(*) AS 'Member #' FROM employee GROUP BY Dept;
    +---------+----------+
    | Dept    | Member # |
    +---------+----------+
    | Power   |        2 |
    | Testing |        3 |
    +---------+----------+
    2 rows in set (0.001 sec)
    
  • GROUP_CONCAT 與 CONCAT , CONCAT_WS
    透過 GROUP_CONCAT 函數,可以把文字整合在一起.

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept;
    +---------+-----------------+
    | Dept    | Member Name     |
    +---------+-----------------+
    | Power   | Afa,Jasmine     |
    | Testing | Ben,Boss,Justin |
    +---------+-----------------+
    2 rows in set (0.001 sec)
    

    透過 GROUP_CONCAT 與 CONCAT 函數,可以把多個欄位文字整合在一起.

    MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(CONCAT(Name,':',Salary) SEPARATOR ',') AS "Member Name: Salary" FROM employee GROUP BY Dept;
    +---------+-----------------------------------+
    | Dept    | Member Name: Salary               |
    +---------+-----------------------------------+
    | Power   | Afa:48000,Jasmine:80000           |
    | Testing | Ben:45000,Boss:75000,Justin:42000 |
    +---------+-----------------------------------+
    2 rows in set (0.003 sec)
    
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料