首页 » SQL 教程 » SQL GROUP BY

SQL GROUP BY

摘要:本教程将向你介绍如何使用 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 列的唯一值的 result set

SELECT 
    fruit
FROM 
    sample_table
GROUP BY 
    fruit;

在实践中，你常常会使用 GROUP BY 从句与 聚合函数搭配使用，例如 MIN、MAX、AVG、SUM 或 COUNT，分别计算每个分组的度量值。

例如，以下内容说明了 GROUP BY 从句与 COUNT 聚合函数配合使用的方式

本例中，我们根据 fruit 列的值对行分组，并将 COUNT 函数应用于 id 列。result set 包括 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 从句的工作原理。

emp_dept_tables

以下示例使用 GROUP BY 从句对 employees 表的 department_id 列中的值进行分组

SELECT 
    department_id
FROM 
    employees
GROUP BY 
    department_id;Code language: SQL (Structured Query Language) (sql)

输出

试一试

+---------------+
| department_id |
+---------------+
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
|             7 |
|             8 |
|             9 |
|            10 |
|            11 |
+---------------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

本例中

  • 首先，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 |
+---------------+-----------+
|             1 |         1 |
|             2 |         2 |
|             3 |         6 |
|             4 |         1 |
|             5 |         7 |
|             6 |         5 |
|             7 |         1 |
|             8 |         6 |
|             9 |         3 |
|            10 |         6 |
|            11 |         2 |
+---------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

工作原理

  • 首先，GROUP BY 从句根据部门 id 将 employees 表中的行分组。
  • 其次，COUNT(employee_id) 返回每组中 employee id 值的数量。

带有 INNER JOIN 的 SQL GROUP BY 示例

以下示例返回按部门分类的员工数量。它使用 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 |
| Administration   |         1 |
| Executive        |         3 |
| Finance          |         6 |
| Human Resources  |         1 |
| IT               |         5 |
| Marketing        |         2 |
| Public Relations |         1 |
| Purchasing       |         6 |
| Sales            |         6 |
| Shipping         |         7 |
+------------------+-----------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

带有 ORDER BY 的 SQL GROUP BY 示例

下面的示例使用 ORDER BY 子句按人数对部门进行排序

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
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

试一试

输出

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Shipping         |         7 |
| Sales            |         6 |
| Finance          |         6 |
| Purchasing       |         6 |
| IT               |         5 |
| Executive        |         3 |
| Marketing        |         2 |
| Accounting       |         2 |
| Human Resources  |         1 |
| Administration   |         1 |
| Public Relations |         1 |
+------------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

请注意，您可以在 ORDER BY 子句中使用 headcount 别名 或 COUNT(employee_id)。

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 headcount > 5
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

试一试

输出

+-----------------+-----------+
| department_name | headcount |
+-----------------+-----------+
| Shipping        |         7 |
| Sales           |         6 |
| Finance         |         6 |
| Purchasing      |         6 |
+-----------------+-----------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

SQL 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 |
| Administration   |    4400.00 |    4400.00 |        4400.00 |
| Executive        |   17000.00 |   24000.00 |       19333.33 |
| Finance          |    6900.00 |   12000.00 |        8600.00 |
| Human Resources  |    6500.00 |    6500.00 |        6500.00 |
| IT               |    4200.00 |    9000.00 |        5760.00 |
| Marketing        |    6000.00 |   13000.00 |        9500.00 |
| Public Relations |   10000.00 |   10000.00 |       10000.00 |
| Purchasing       |    2500.00 |   11000.00 |        4150.00 |
| Sales            |    6200.00 |   14000.00 |        9616.67 |
| Shipping         |    2700.00 |    8200.00 |        5885.71 |
+------------------+------------+------------+----------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

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 |
| Administration   |      4400.00 |
| Executive        |     58000.00 |
| Finance          |     51600.00 |
| Human Resources  |      6500.00 |
| IT               |     28800.00 |
| Marketing        |     19000.00 |
| Public Relations |     10000.00 |
| Purchasing       |     24900.00 |
| Sales            |     57700.00 |
| Shipping         |     41200.00 |
+------------------+--------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

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(employee_id) |
+------------------+---------------------------------+--------------------+
| Accounting       | Accounting Manager              |                  1 |
| Accounting       | Public Accountant               |                  1 |
| Administration   | Administration Assistant        |                  1 |
| Executive        | Administration Vice President   |                  2 |
| Executive        | President                       |                  1 |
| Finance          | Accountant                      |                  5 |
| Finance          | Finance Manager                 |                  1 |
| Human Resources  | Human Resources Representative  |                  1 |
| IT               | Programmer                      |                  5 |
| Marketing        | Marketing Manager               |                  1 |
| Marketing        | Marketing Representative        |                  1 |
| Public Relations | Public Relations Representative |                  1 |
| Purchasing       | Purchasing Clerk                |                  5 |
| Purchasing       | Purchasing Manager              |                  1 |
| Sales            | Sales Manager                   |                  2 |
| Sales            | Sales Representative            |                  4 |
| Shipping         | Shipping Clerk                  |                  2 |
| Shipping         | Stock Clerk                     |                  1 |
| Shipping         | Stock Manager                   |                  4 |
+------------------+---------------------------------+--------------------+
19 rows in set (0.00 sec)Code language: PHP (php)

小结

  • GROUP BY 子句根据一列或多列的值将行分组到组中。
  • 使用 GROUP BY 子句的聚合函数来计算每个组的汇总值。
本教程是否帮到了您？
SQL CROSS JOIN
先前
SQL CROSS JOIN
后续
SQL HAVING
SQL HAVING