摘要：在本教程中，您將學習如何使用 SQL GROUP BY 子句根據一個或多個列對行進行分組。
SQL GROUP BY 子句簡介 #
GROUP BY 是 SELECT 語句的一個可選子句。GROUP BY 子句允許您根據一個或多個列的值對行進行分組。它為每個分組返回一行。
下面展示了 GROUP BY 子句的基本語法
SELECT
column1,
column2,
aggregate_function (column3)
FROM
table_name
GROUP BY
column1,
column2;Code language: SQL (Structured Query Language) (sql)下圖說明了 GROUP BY 子句的工作原理
左側的表有兩列：id 和 fruit。當您將 GROUP BY 子句應用於 fruit 列時，它會返回一個包含 fruit 列中唯一值的結果集。
SELECT
fruit
FROM
sample_table
GROUP BY
fruit;在實踐中，您通常會將 GROUP BY 子句與聚合函式（如 MIN、MAX、AVG、SUM 或 COUNT）一起使用，以計算為每個分組提供資訊的度量值。
例如，下面說明了 GROUP BY 子句如何與 COUNT 聚合函式一起工作
在此示例中，我們按 fruit 列的值對行進行分組，並將 COUNT 函式應用於 id 列。結果集包括 fruit 列的唯一值以及相應行的數量。
SELECT
fruit,
COUNT(id)
FROM
sample_table
GROUP BY
fruit;出現在 GROUP BY 子句中的列稱為分組列。如果分組列包含 NULL 值，所有 NULL 值都會被彙總到一個分組中，因為 GROUP BY 子句認為所有 NULL 值都是相等的。
SQL GROUP BY 示例 #
我們將使用示例資料庫中的 employees 和 departments 表來演示 GROUP BY 子句的工作原理。
以下示例使用 GROUP BY 子句對 employees 表中 department_id 列的值進行分組
SELECT
department_id
FROM
employees
GROUP BY
department_id
ORDER BY
department_id;Code language: SQL (Structured Query Language) (sql)輸出
department_id
---------------
1
2
3
4
5
6
7
8
9
10
11在此示例中：
- 首先，
SELECT子句返回employees表中department_id列的所有值。 - 其次，
GROUP BY子句將所有值分組。
employees 表的 department_id 列有 40 行，包括重複的 department_id 值。但是，GROUP BY 將這些值分組。
在沒有聚合函式的情況下，GROUP BY 的行為類似於 DISTINCT 關鍵字
SELECT DISTINCT
department_id
FROM
employees
ORDER BY
department_id;Code language: SQL (Structured Query Language) (sql)當您將 GROUP BY 子句與聚合函式一起使用時，它會更有用。
例如，以下語句使用 GROUP BY 子句和 COUNT 函式來按部門統計員工人數
SELECT
department_id,
COUNT(employee_id) headcount
FROM
employees
GROUP BY
department_id;Code language: SQL (Structured Query Language) (sql)輸出
department_id | headcount
---------------+-----------
11 | 2
9 | 3
3 | 6
5 | 7
4 | 1
10 | 6
6 | 5
2 | 2
7 | 1
1 | 1
8 | 6它的工作原理如下：
- 首先，
GROUP BY子句按部門 ID 對employees表中的行進行分組。 - 其次，
COUNT(employee_id)返回每個分組中員工 ID 值的數量。
SQL GROUP BY 與 INNER JOIN 示例 #
以下示例返回按部門統計的員工人數。它使用 INNER JOIN 子句在結果中包含部門名稱。
SELECT
department_name,
COUNT(employee_id) headcount
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
department_name;Code language: SQL (Structured Query Language) (sql)輸出
department_name | headcount
------------------+-----------
Accounting | 2
Purchasing | 6
Marketing | 2
Administration | 1
Finance | 6
Human Resources | 1
Public Relations | 1
Executive | 3
Shipping | 7
Sales | 6
IT | 5Code language: PHP (php)SQL GROUP BY 與 HAVING 示例 #
以下示例使用 HAVING 子句查詢員工人數大於 5 的部門。
SELECT
department_name,
COUNT(employee_id) headcount
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
department_name
HAVING
COUNT(employee_id) > 5
ORDER BY
headcount DESC;Code language: SQL (Structured Query Language) (sql)輸出
department_name | headcount
-----------------+-----------
Shipping | 7
Purchasing | 6
Finance | 6
Sales | 6SQL GROUP BY 與 MIN、MAX 和 AVG 示例 #
SELECT
department_name,
MIN(salary) min_salary,
MAX(salary) max_salary,
ROUND(AVG(salary), 2) average_salary
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
department_name;Code language: SQL (Structured Query Language) (sql)輸出
department_name | min_salary | max_salary | average_salary
------------------+------------+------------+----------------
Accounting | 8300.00 | 12000.00 | 10150.00
Purchasing | 2500.00 | 11000.00 | 4150.00
Marketing | 6000.00 | 13000.00 | 9500.00
Administration | 4400.00 | 4400.00 | 4400.00
Finance | 6900.00 | 12000.00 | 8600.00
Human Resources | 6500.00 | 6500.00 | 6500.00
Public Relations | 10000.00 | 10000.00 | 10000.00
Executive | 17000.00 | 24000.00 | 19333.33
Shipping | 2700.00 | 8200.00 | 5885.71
Sales | 6200.00 | 14000.00 | 9616.67
IT | 4200.00 | 9000.00 | 5760.00Code language: PHP (php)SQL GROUP BY 與 SUM 函式示例 #
要獲取每個部門的總工資，您可以將 SUM 函式應用於 salary 列，並按 department_id 列對員工進行分組，如下所示
SELECT
department_name,
SUM(salary) total_salary
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
department_name;Code language: SQL (Structured Query Language) (sql)輸出
department_name | total_salary
------------------+--------------
Accounting | 20300.00
Purchasing | 24900.00
Marketing | 19000.00
Administration | 4400.00
Finance | 51600.00
Human Resources | 6500.00
Public Relations | 10000.00
Executive | 58000.00
Shipping | 41200.00
Sales | 57700.00
IT | 28800.00Code language: PHP (php)SQL GROUP BY 多列 #
到目前為止，您已經看到我們按一列對所有員工進行了分組。例如，以下子句將 department_id 列中具有相同值的所有行放在一個分組中。
GROUP BY department_idCode language: SQL (Structured Query Language) (sql)如何按 department_id 和 job_id 兩列的值對員工進行分組呢？
GROUP BY department_id, job_idCode language: SQL (Structured Query Language) (sql)此子句將 department_id 和 job_id 兩列中具有相同值的所有員工分在同一個組中。
以下語句將 department_id 和 job_id 兩列中具有相同值的行分在同一個組中，然後為每個組返回一行。
SELECT
department_name,
job_title,
COUNT(employee_id)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
INNER JOIN jobs j ON j.job_id = e.job_id
GROUP BY
department_name,
job_title;Code language: SQL (Structured Query Language) (sql) department_name | job_title | count
------------------+---------------------------------+-------
Purchasing | Purchasing Clerk | 5
Public Relations | Public Relations Representative | 1
Administration | Administration Assistant | 1
Marketing | Marketing Representative | 1
Shipping | Shipping Clerk | 2
Shipping | Stock Clerk | 1
Purchasing | Purchasing Manager | 1
IT | Programmer | 5
Accounting | Accounting Manager | 1
Accounting | Public Accountant | 1
Finance | Accountant | 5
Sales | Sales Manager | 2
Human Resources | Human Resources Representative | 1
Sales | Sales Representative | 4
Executive | President | 1
Shipping | Stock Manager | 4
Marketing | Marketing Manager | 1
Finance | Finance Manager | 1
Executive | Administration Vice President | 2Code language: PHP (php)摘要 #
GROUP BY子句根據一個或多個列的值將行分組。- 使用
GROUP BY子句和聚合函式來計算每個分組的彙總值。
資料庫 #
- PostgreSQL GROUP BY 子句
- Oracle GROUP BY 子句
- SQL Server GROUP BY 子句
- MySQL GROUP BY 子句
- SQLite GROUP BY 子句
- Db2 GROUP BY 子句
- MariaDB GROUP BY 子句
