Mysql with 用法

什么是 with 语句

WITH 子句是 MySQL 中的一种 SQL 结构，又称为 Common Table Expression (CTE)。它在不影响原有 SQL 语句的情况下，允许开发人员临时创建一个内存中的结果集，然后对其进行操作。

with 语句用法

语法格式

WITH cte_name (column_name1, column_name2, ...) AS ( SELECT column1, column2, ... FROM table WHERE condition )

其中，cte_name 是 WITH 子句的名称；column_name1、column_name2 等是结果集的列名；SELECT 子句定义了该结果集的内容；condition 是查询的过滤条件。

使用样例1

可以根据 limit 实现 从学生表中找出 班级id 然后 删除班级表中对应的数据 控制limit的数量 分批删除数据。

WITH cbt AS ( SELECT class_id FROM t_user LIMIT 10 ) DELETE 
FROM
	t_class 
WHERE
	class_id IN ( SELECT class_id FROM cte );

非递归派生表

# 使用派生表子查询
SELECT
	max( txt ) max_txt,
	min( txt ) min_txt 
FROM
	(
	SELECT
		concat( cte2.txt, cte3.txt ) AS txt 
	FROM
		( SELECT concat( cte1.txt, 'is a ' ) AS txt FROM ( SELECT 'this ' AS txt ) AS cte1 ) AS cte2,
		( SELECT 'nice query' AS txt UNION SELECT 'query that rocks' UNION SELECT 'query' ) AS cte3 
	) AS cte4;
						
						
# 使用with非递归查询
WITH cte1 AS ( SELECT 'this ' AS txt ),
cte2 AS ( SELECT concat( cte1.txt, 'is a' ) AS txt FROM cte1 ),
cte3 AS ( SELECT 'nice query' AS txt UNION SELECT 'query that rocks' AS txt UNION SELECT 'query' AS txt ),
cte4 AS ( SELECT concat( cte2.txt, cte3.txt ) AS txt FROM cte2, cte3 ) SELECT
max( txt ) max_txt,
min( txt ) min_txt 
FROM
	cte4;