公用表达式

1.普通公用表达式：

1 #CTE配合使用实现子查询
2 WITH cte_emp
3 AS (SELECT DISTINCT department_id FROM employees)
4 #此处cte_emp就可以表示子查询中()里面的表
5 
6 SELECT *
7 FROM departments d JOIN cte_emp e
8 ON d.department_id = e.department_id;

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

 

2.递归公用表达式

 1 #举例：找出employee表中所有的下属(包括下属的下属等等)
 2 WITH RECURSIVE cte
 3 AS
 4 (
 5 SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100#种子查询，找到递归开始的起点
 6 UNION ALL
 7 SELECT a.employee_id,a.last_name,a.manager_id, n+1 FROM employees AS a JOIN cte
 8 ON (a.manager_id = cte.employee_id)#定义递归的方式，逐一向下递归，直到无法继续向下则停止递归
 9 )
10 SELECT last_name,employee_id FROM cte WHERE n >= 3;

 







            

            
posted @ 
2022-04-02 14:16 
jue1e0 
阅读(67) 
评论(0) 
 
收藏 
举报