SQL常用语法( WITH 语句)

在 SQL 中，WITH 语句用于定义公用表表达式（Common Table Expressions，CTE）。CTE 是一种临时结果集，可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用。它使得复杂查询更加易于理解，并且可以提高查询的可读性和结构性。通过使用 CTE，可以将查询逻辑进行分解，避免嵌套查询的复杂性。

CTE 的基本语法

CTE 的基本语法如下：

WITH cte_name AS (
    -- 这里是一个 SQL 查询
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

在这个语法中： - cte_name 是你为 CTE 起的名称，可以在随后的查询中使用。 - 在括号内是具体的 SQL 查询，这个查询会生成一个临时结果集，可以在后续的查询中使用。

示例

假设我们有一个员工表 employees 和一个部门表 departments。我们希望查询每个部门的员工总数及其平均工资。为了使查询更清晰，我们可以使用 CTE。

WITH department_stats AS (
    SELECT 
        d.department_id,
        d.department_name,
        COUNT(e.employee_id) AS employee_count,
        AVG(e.salary) AS average_salary
    FROM 
        departments d
    LEFT JOIN 
        employees e ON d.department_id = e.department_id
    GROUP BY 
        d.department_id, d.department_name
)
SELECT 
    department_id,
    department_name,
    employee_count,
    average_salary
FROM 
    department_stats;

在这个例子中： 1. department_stats CTE 汇总了每个部门的员工数量和平均工资。 2. 然后我们从 department_stats 中选择所需的字段。这样可以避免在主查询中重复书写 JOIN 和聚合逻辑。

递归 CTE

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

WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT column1, column2 
    FROM table_name 
    WHERE condition

    UNION ALL

    -- 递归查询
    SELECT column1, column2 
    FROM table_name 
    JOIN cte_name ON table_name.foreign_key = cte_name.primary_key
)
SELECT * FROM cte_name;

递归 CTE 示例

假设我们有一个 employees 表，包含员工及其上级的关系，现在我们希望查询一个特定员工的所有下级员工。

WITH RECURSIVE employee_hierarchy AS (
    -- 基础查询：查找特定员工
    SELECT employee_id, manager_id
    FROM employees
    WHERE employee_id = 1  -- 假设我们查找员工 ID 为 1 的员工

    UNION ALL

    -- 递归查询：查找该员工的所有下属
    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

在这个示例中，CTE employee_hierarchy 递归地查找员工 ID 为 1 的所有下属员工。基础查询获取最初的员工，递归部分则通过自 JOIN 获取所有下属。

总结

使用 CTE 不仅可以提升 SQL 查询的可读性，还能使得复杂的查询逻辑更加简洁。无论是普通的聚合查询，还是需要递归的层级查询，CTE 都能有效地提供解决方案。通过合理使用 CTE，开发者能够更清晰地表达他们的业务逻辑，写出更为高效的 SQL 代码。