SQL中的公用表表达式
公用表表达式（CTE）
相当于可以复用的子查询
-
普通公用表表达式
# 查询员工表中所有员工的部门详细信息 WITH cte_emp AS (SELECT DISTINCT department_id FROM employees); SELECT * FROM departments d JOIN cte_emp e ON d.department_id = e.department_id; -
递归公用表表达式
# 找出公司中所有的下下属 # 找到第一代管理者（老大） WITH RECURSIVE cte AS (SELECT employee_id, last_name, manager_id, 1 AS n FROM employees WHERE employee_id = 100; UNION ALL SELECT a.employee_id, a.last_name, a.manager_id, n+1 FROM employees AS a JOIN cte ON a.manager_id = cte.employee_id ) SELECT employee_id, last_name FROM cte WHERE n >= 3;