【PostgreSQL 17】10 通用表表达式（CTF）

优点

• 提高复杂查询的可读性
• 支持递归查询，方便处理层次结构数据和图数据

简单示例

WITH t(n) AS (
	SELECT 1
)
SELECT *
FROM t;

WITH t1(n) AS (
	SELECT 1
),
t2(m) AS (
	SELECT 2
)
SELECT *
FROM t1 CROSS JOIN t2
;

应用场景

1 子查询模块化

查询部门名及平均薪资
一般子查询写法：

SELECT
	d.department_name,
	ds.avg_sal
FROM
	departments d
JOIN (
	SELECT
		department_id,
		avg(salary) avg_sal
	FROM
		employees
	GROUP BY
		department_id
) ds
ON
	d.department_id = ds.department_id
;

等价的CTE写法：

WITH department_avg(department_id, avg_sal) AS (
	SELECT
		department_id,
		avg(salary) avg_sal
	FROM
		employees
	GROUP BY
		department_id
)

SELECT
	d.department_name,
	da.avg_sal
FROM
	departments d
JOIN
	department_avg da
ON
	d.department_id = da.department_id
;

2 递归

WITH RECURSIVE t(n) AS (
	SELECT 1      -- 初始化
	UNION ALL
	SELECT n+1
	FROM t        -- 递归
	WHERE n < 10  -- 不设置这个条件会死循环
)
SELECT *
FROM t
;

查询员工层级关系

WITH RECURSIVE emp_path(emp_id, emp_name, path) AS (
	SELECT
		employee_id,
		first_name || '·' || last_name,  
		first_name || '·' || last_name   
	FROM
		employees
	WHERE
		manager_id IS NULL
	UNION ALL
	SELECT
		employee_id,
		first_name || '·' || last_name, 
		path || '->' || first_name || '·' || last_name 
	FROM
		employees e
	JOIN
		emp_path p
	ON
		e.manager_id = p.emp_id
)

SELECT * FROM emp_path;

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第31~32节