測試環境為 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 的使用方式,詳細使用方式請參考 – https://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)
沒有解決問題,試試搜尋本站其他內容