SQL备忘--通用表表达式

概念

有时在一个SQL内，会存在多个相同的子查询，要反复查询同一份数据。
此种情况可以把子查询抽离出来，做成通用表表达式，在SQL内像使用变量一样，多次引用子查询的结果，来更好的精简SQL，提升性能和可读性

语法

-- 将子查询封装为一个通用表表达式
WITH 通用表表达式名(列1, 列2...) AS (			-- 类似一个临时表，括号内是列名，数量上应与子查询列数量一致
	子查询语句					
)
SELECT 列1, 列2
FROM 通用表表达式名					-- 在sql中使用通用表表达式，类似表的用法

实例

WITH cte_a(c1, c2) AS (			
	select col1, col2 FROM t_1		
),
WITH cte_b(c2) AS (				-- 另一个通用表表达式
	select c2 FROM t_2							
)
SELECT cte_a.c1 
FROM cte_a,						
cte_b
WHERE cte_a.c2 = cte_b.c2

mysql需要8.0以上才有通用表表达式

递归查询

通用表表达式可以实现递归调用，来实现子查询的复合查询

语法

-- 最终会得到一个多次执行结果并起来的复合结果，比如多列结果
WITH RECURSIVE 通用表表达式名 AS (			-- 
	初始化部分语句							-- 可以理解sql会先执行这个语句，得到一个初始化结果
	UNION ALL
	递归部分语句								-- 随后不断的用之前的结果调用此语句，得到新结果，并UNION并起来			
)

举例

得到1到10的数列

-- 最终会得到一个多次执行结果并起来的复合结果，比如多列结果
WITH RECURSIVE c_t(n) AS (			-- 
	SELECT 1
	UNION ALL
	SELECT n + 1 				-- 递归时将上一条数据加1
	FROM c_t 					-- 递归子查询的表名和列名应该与通用表表达式的名称和列名一致		
	WHERE n < 10				-- 要做限制，不然会无限递归
)

Oracle和SQL Server也支持递归，但要去掉语法中RECURSIVE 关键字