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，专门用来处理层级数据，比如组织架构、产品分类等等。
假设你有一张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的可读性和可维护性。但也要注意性能问题，根据实际情况进行优化。
