跳到主要内容

SQL WITH子句

在SQL中，WITH子句（也称为公共表表达式，Common Table Expression，简称CTE）是一种强大的工具，用于创建临时结果集，这些结果集可以在后续的查询中引用。WITH子句特别适合处理复杂的查询，因为它可以将查询分解为更小、更易管理的部分，从而提高代码的可读性和可维护性。

什么是WITH子句？​

WITH子句允许你在一个查询中定义一个或多个临时表（称为CTE），这些临时表可以在主查询中像普通表一样使用。CTE的生命周期仅限于当前查询的执行期间，查询结束后，CTE就会被销毁。

WITH子句的基本语法如下：

sql
WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

示例：简单的WITH子句​

假设我们有一个名为employees的表，包含以下数据：

idnamedepartmentsalary
1AliceHR50000
2BobIT60000
3CharlieHR55000
4DavidIT70000

我们可以使用WITH子句来计算每个部门的平均工资：

sql
WITH department_avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM department_avg_salary;

输出：

departmentavg_salary
HR52500
IT65000

在这个例子中，department_avg_salary是一个CTE，它计算了每个部门的平均工资。然后，主查询从这个CTE中选择所有数据。

使用多个CTE​

WITH子句允许你定义多个CTE，这些CTE可以在同一个查询中相互引用。例如，我们可以扩展上面的例子，计算每个部门中工资高于平均工资的员工：

sql
WITH department_avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
high_earners AS (
    SELECT e.name, e.department, e.salary
    FROM employees e
    JOIN department_avg_salary d
    ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT * FROM high_earners;

输出：

namedepartmentsalary
BobIT60000
DavidIT70000

在这个例子中，我们定义了两个CTE：department_avg_salary和high_earners。第一个CTE计算了每个部门的平均工资，第二个CTE则筛选出工资高于部门平均工资的员工。

递归CTE​

WITH子句还支持递归查询，这在处理层次结构数据（如组织结构或树形结构）时非常有用。递归CTE的基本语法如下：

sql
WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT ...
    UNION ALL
    -- 递归查询
    SELECT ...
)
SELECT ... FROM cte_name;

示例：递归CTE​

假设我们有一个表示员工及其经理的表employee_hierarchy：

idnamemanager_id
1AliceNULL
2Bob1
3Charlie2
4David2

我们可以使用递归CTE来查找某个员工的所有下属：

sql
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employee_hierarchy
    WHERE name = 'Bob'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employee_hierarchy e
    INNER JOIN subordinates s
    ON e.manager_id = s.id
)
SELECT * FROM subordinates;

输出：

idnamemanager_id
2Bob1
3Charlie2
4David2

在这个例子中，递归CTE首先选择Bob作为基础查询，然后递归地查找Bob的所有下属。

实际应用场景​

WITH子句在以下场景中非常有用：

  1. 复杂查询的分解：当查询逻辑非常复杂时，可以使用WITH子句将其分解为多个简单的部分。
  2. 递归查询：处理层次结构数据时，递归CTE是必不可少的工具。
  3. 代码复用：如果多个查询需要相同的子查询结果，可以使用WITH子句定义CTE，避免重复代码。

总结​

WITH子句是SQL中一个非常有用的工具，它可以帮助你简化复杂查询，提高代码的可读性和可维护性。通过定义临时结果集（CTE），你可以将查询分解为更小的部分，并在主查询中引用这些部分。此外，递归CTE使得处理层次结构数据变得更加容易。

提示

在实际开发中，尽量使用WITH子句来分解复杂查询，这样不仅可以让代码更易读，还能提高查询的性能。

附加资源与练习​

  • 练习1：使用WITH子句编写一个查询，计算每个部门的最高工资，并找出工资最高的员工。
  • 练习2：使用递归CTE查找某个员工的所有上级经理。

通过练习这些例子，你将更好地掌握WITH子句的使用方法。