公用表达式

1.普通公用表达式：

#CTE配合使用实现子查询
WITH cte_emp
AS (SELECT DISTINCT department_id FROM employees)
#此处cte_emp就可以表示子查询中()里面的表

SELECT *
FROM departments d JOIN cte_emp e
ON d.department_id = e.department_id;

即用cte_emp来表示()中SELECT DISTINCT department_id FROM employees的内容

 

2.递归公用表达式

#举例：找出employee表中所有的下属(包括下属的下属等等)
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 last_name,employee_id FROM cte WHERE n >= 3;