postgresql-通用表达式

入门案例

-- 通用表达式
with t(n) as 
(select 2)
select * from t;
1
2
3
4

简单CTE

WITH cte_name (col1, col2, ...) AS (
 cte_query_definition
)
sql_statement;
1
2
3
4

WITH 表示定义 CTE，因此 CTE 也称为 WITH 查询；
cte_name 指定了 CTE 的名称，后面是可选的字段名
括号内是 CTE 的内容，可以是 SELECT 语句，也可以是 INSERT、UPDATE、DELETE
语句；
sql_statement 是主查询语句，可以引用前面定义的 CTE。该语句同样可以是 SELECT、
INSERT、UPDATE 或者 DELETE

select 
d.department_name,
ds.avg_sal
from departments d
join (
select 
e.department_id ,avg(e.salary) avg_sal
from employees e 
group by e.department_id
) ds 
on (d.department_id = ds.department_id);
1
2
3
4
5
6
7
8
9
10
11

with department_avg(deparment_id,avg_sal) as
(
	select 
	e.department_id ,avg(e.salary) as avg_sal
	from employees e 
	group by e.department_id 
)
select 
d.department_name,
da.avg_sal
from departments d 
join department_avg da 
on (d.department_id = da.deparment_id);
1
2
3
4
5
6
7
8
9
10
11
12
13

递归 CTE

WITH RECURSIVE cte_name AS(
 cte_query_initial -- 初始化部分
 UNION [ALL]
 cte_query_iterative -- 递归部分
) SELECT * FROM cte_name;
1
2
3
4
5

recursive 表示递归；
cte_query_initial 是初始化查询，用于创建初始结果集；
cte_query_iterative 是递归部分，可以引用 cte_name；
如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归并返回结果

案例1

-- 递归生成数字序列
with recursive t(n) as (
 select 1 -- 初始化
 union all
 select n+1 from t where n < 10-- 递归
) select * from t;
1
2
3
4
5
6

以上sql语句执行过程:
1.执行 CTE 中的初始化查询，生成一行数据（1）
2. 第一次执行递归查询，判断 n < 10 成立，生成一行数据 2（n+1）
3. 重复执行递归查询，生成更多的数据；直到 n = 10 终止；此时临时表 t 中包含 10 条数据；
4. 执行主查询，返回所有的数据
5. 如果没有指定终止条件，上面的查询将会进入死循环

案例2

with recursive emp_path(emp_id,emp_name,path) as (
  select 
  e.employee_id, e.first_name||','||e.last_name,e.first_name||','||e.last_name 
  from employees e
  where e.manager_id is null 
  union all
  select 
  e2.employee_id , e2.first_name||','||e2.last_name,path||'->'||e2.first_name||','||e2.last_name 
  from employees e2
  join emp_path p on (e2.manager_id = p.emp_id)
)
select * from emp_path
1
2
3
4
5
6
7
8
9
10
11
12

初始化查询语句返回了公司最高层的领导（manager_id IS NULL），也就是
“Steven,King”；递归查询将员工表的 manager_id 与已有结果集中的 employee_id 关联，获取每个
员工的下一级员工，直到无法找到新的数据；path 字段存储了每个员工从上至下的管理路径

参考文章