sql中with子句的作用 with临时查询提升SQL可读性的方法

with子句通过定义临时结果集提升sql可读性，其核心用法是创建公共表表达式（cte），如查询部门最高工资员工时，先用cte departmentmaxsalary找出最高工资，再用employeewithrank筛选出排名靠前的员工；with recursive用于处理层级数据，例如查找某员工的所有下属，通过递归查询逐层展开组织结构；性能优化方面需注意避免过度使用、合理索引、了解物化策略、避免循环使用及合理拆分复杂cte，以确保查询效率。

with子句，说白了，就是给一段SQL查询结果起个别名，让你在后面的查询里像用表一样用它。这玩意儿最大的好处，就是把复杂的SQL拆解成小块，可读性蹭蹭往上涨。

提升SQL可读性，with子句绝对是利器。

如何使用WITH子句创建临时表？

WITH子句的核心用法就是定义临时结果集，也叫公共表表达式（Common Table Expression，CTE）。这玩意儿，就好像你在SQL里临时创建了一个视图，但这个视图只在当前SQL语句里有效。

举个例子，假设你要查出每个部门工资最高的员工信息。没用WITH之前，你可能要嵌套好几层查询，看得人眼花缭乱。用了WITH，就能这样：

WITH DepartmentMaxSalary AS (
    SELECT
        department_id,
        MAX(salary) AS max_salary
    FROM
        employees
    GROUP BY
        department_id
),
EmployeeWithRank AS (
    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.department_id,
        e.salary,
        DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as salary_rank
    FROM
        employees e
    JOIN
        DepartmentMaxSalary dms ON e.department_id = dms.department_id AND e.salary = dms.max_salary
)
SELECT
    employee_id,
    first_name,
    last_name,
    department_id,
    salary
FROM
    EmployeeWithRank
WHERE salary_rank = 1;

这里，DepartmentMaxSalary CTE负责找出每个部门的最高工资，EmployeeWithRank CTE则基于这个结果，找出每个部门工资最高的员工，并使用DENSE_RANK()函数进行排序，最后主查询再筛选出排名第一的员工。是不是清晰多了？

WITH RECURSIVE在处理层级数据中的应用？

WITH子句还有个高级用法，就是WITH RECURSIVE，专门用来处理层级数据，比如组织架构、产品分类等等。

koly.club

一站式社群管理工具

下载

假设你有一张employees表，包含员工ID、姓名和上级领导ID。你要查出某个员工的所有下属，包括直接下属和间接下属。不用递归，这几乎是不可能完成的任务。但有了WITH RECURSIVE，就简单多了：

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        manager_id,
        1 AS level
    FROM
        employees
    WHERE
        employee_id = 100 -- 假设员工ID为100是根节点

    UNION ALL

    SELECT
        e.employee_id,
        e.first_name,
        e.last_name,
        e.manager_id,
        eh.level + 1
    FROM
        employees e
    JOIN
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT
    employee_id,
    first_name,
    last_name,
    level
FROM
    EmployeeHierarchy;

这个SQL里，EmployeeHierarchy CTE首先选出根节点员工的信息，然后通过UNION ALL和自身连接，不断找出下级员工，直到没有下级为止。level字段记录了员工的层级关系。

WITH子句的性能考量与优化技巧？

WITH子句虽然好用，但也要注意性能问题。每次使用WITH，数据库都会创建一个临时表，如果数据量很大，或者WITH子句嵌套太多，可能会影响查询效率。

优化WITH子句，可以考虑以下几点：

• 避免过度使用： 不要为了用而用，只有在能显著提高可读性的情况下才使用WITH。
• 索引优化： 确保WITH子句中用到的字段都有合适的索引。
• 物化策略： 不同的数据库对WITH子句的物化策略不同，有些数据库会把WITH子句的结果物化成临时表，有些则会直接内联到主查询中。了解数据库的物化策略，可以更好地优化查询。
• 避免在循环中使用： 尽量避免在循环中使用WITH子句，这会导致重复创建临时表，影响性能。
• 合理拆分： 如果WITH子句过于复杂，可以考虑将其拆分成多个更小的WITH子句，或者使用临时表来代替。

总之，WITH子句是SQL优化的一个重要工具，用好了能大大提高SQL的可读性和可维护性。但也要注意性能问题，根据实际情况进行优化。