什么是 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 
FROMt_class 
WHEREclass_id IN ( SELECT class_id FROM cte );

非遞歸派生表

# 使用派生表子查詢
SELECTmax( txt ) max_txt,min( txt ) min_txt 
FROM(SELECTconcat( 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 
FROMcte4;