WITH在MYSQL中的用法示例詳解
更新時間：2025年05月06日 10:53:35 作者：木木子9999
WITH 子句（也稱為公共表表達式,Common Table Expression,簡稱 CTE）是 SQL 中一種強大的查詢構建工具,它可以顯著提高復雜查詢的可讀性和可維護性,這篇文章主要介紹了WITH在MYSQL中的用法,需要的朋友可以參考下
WITH 子句（也稱為公共表表達式，Common Table Expression，簡稱 CTE）是 SQL 中一種強大的查詢構建工具，它可以顯著提高復雜查詢的可讀性和可維護性。
一、基本語法結構
WITH cte_name AS (
SELECT ... -- 定義CTE的查詢
)
SELECT ... FROM cte_name; -- 主查詢使用CTE二、CTE 的核心特點
- 臨時結果集：CTE 只在當前查詢執(zhí)行期間存在
- 可引用性：定義后可在主查詢中多次引用
- 作用域限制：僅在緊隨其后的單個語句中有效
三、MySQL 中 CTE 的具體用法
1. 基本 CTE（單表表達式）
WITH sales_summary AS (
SELECT product_id, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_id
)
SELECT p.product_name, s.total_sold
FROM products p
JOIN sales_summary s ON p.product_id = s.product_id;2. 多 CTE 定義（逗號分隔）
WITH
customer_orders AS (
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
),
high_value_customers AS (
SELECT customer_id
FROM customer_orders
WHERE order_count > 5
)
SELECT c.customer_name
FROM customers c
JOIN high_value_customers h ON c.customer_id = h.customer_id;3. 遞歸 CTE（MySQL 8.0+ 支持）
遞歸 CTE 用于處理層次結構數(shù)據(jù)：
WITH RECURSIVE org_hierarchy AS (
-- 基礎查詢（錨成員）
SELECT id, name, parent_id, 1 AS level
FROM organization
WHERE parent_id IS NULL
UNION ALL
-- 遞歸查詢（遞歸成員）
SELECT o.id, o.name, o.parent_id, h.level + 1
FROM organization o
JOIN org_hierarchy h ON o.parent_id = h.id
)
SELECT * FROM org_hierarchy;四、CTE 的優(yōu)勢
提高可讀性：
- 將復雜查詢分解為邏輯塊
- 類似編程中的變量定義
避免重復子查詢：
-- 不使用CTE（重復子查詢） SELECT * FROM (SELECT ... FROM table1) AS t1 JOIN (SELECT ... FROM table1) AS t2... -- 使用CTE（避免重復） WITH t1 AS (SELECT ... FROM table1) SELECT * FROM t1 JOIN t1 AS t2...
支持遞歸查詢：處理樹形/層次結構數(shù)據(jù)
五、CTE 與臨時表的區(qū)別
| 特性 | CTE | 臨時表 |
|---|---|---|
| 生命周期 | 僅當前語句有效 | 會話結束前有效 |
| 存儲 | 不物理存儲 | 可能存儲在內存或磁盤 |
| 索引 | 不能創(chuàng)建索引 | 可以創(chuàng)建索引 |
| 可見性 | 僅定義它的查詢可見 | 同一會話的后續(xù)查詢可見 |
| 性能 | 優(yōu)化器可能內聯(lián)展開 | 需要實際創(chuàng)建和填充 |
六、實際應用場景
1. 復雜報表查詢
WITH monthly_sales AS (...), product_ranking AS (...) SELECT ... FROM monthly_sales JOIN product_ranking...
2. 數(shù)據(jù)清洗管道
WITH raw_data AS (...), cleaned_data AS (...), enriched_data AS (...) SELECT * FROM enriched_data;
3. 層次結構遍歷（組織架構、評論線程等）
WITH RECURSIVE comment_tree AS (...) SELECT * FROM comment_tree;
七、性能注意事項
物化提示：
WITH cte_name AS (
SELECT /*+ MATERIALIZE */ ... -- 強制物化
)合并提示：
WITH cte_name AS (
SELECT /*+ MERGE */ ... -- 強制合并到主查詢
)遞歸深度控制（MySQL 默認 1000）：
SET @@cte_max_recursion_depth = 2000;
八、版本兼容性
- MySQL 8.0+ 完整支持 CTE 和遞歸 CTE
- MySQL 5.7 及更早版本不支持 CTE
WITH 子句是現(xiàn)代 SQL 開發(fā)中不可或缺的工具，合理使用可以大幅提升查詢的清晰度和維護性，特別是在處理多層嵌套或遞歸數(shù)據(jù)時。
到此這篇關于WITH在MYSQL中的用法示例詳解的文章就介紹到這了,更多相關mysql with用法內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
MySQL操作數(shù)據(jù)庫和表的常用命令新手教程
這篇文章主要介紹了MySQL操作數(shù)據(jù)庫和表的常用命令新手教程,本文總結的命令都是控制mysql必須掌握的、常用的命令,需要的朋友可以參考下2014-09-09
mysql 數(shù)據(jù)庫中my.ini的優(yōu)化 2G內存針對站多 抗壓型的設置
mysql數(shù)據(jù)庫中my.ini的優(yōu)化,2G內存,針對站多,抗壓型的設置.大家可以借鑒下。2009-08-08
眾所周知MySQL 的主鍵可以是自增的,下面這篇文章主要給大家介紹了關于MySQL8自增主鍵變化的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-04-04
SQL中from_unixtime函數(shù)的使用方法實例
在MySQL數(shù)據(jù)表設計中,時間字段一般都設計為時間戳格式的,開發(fā)人員去查看的時候就顯得有點不方便,可以使用FROM_UNIXTIME轉換成日期格式進行查看,下面這篇文章主要給大家介紹了關于SQL中from_unixtime函數(shù)的使用方法的相關資料,需要的朋友可以參考下2022-08-08
這篇文章主要介紹了MySQL表自增id溢出的故障復盤解決，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-04-04
MySQL默認是不區(qū)分大小寫的,這個涉及到collate字符集與校驗規(guī)則方面的知識,下面會講到,這里我們就來詳解MySQL查詢時區(qū)分字符串中字母大小寫的方法.2016-05-05
索引失效指的是在進行查詢操作時,本應該使用索引來提升查詢效率的場景下,數(shù)據(jù)庫沒有利用索引,而是采用了全表掃描的方式,這會大大增加查詢時間和系統(tǒng)負擔,這篇文章主要介紹了MySQL索引失效的幾種常見場景的相關資料,需要的朋友可以參考下2025-09-09
這篇文章主要介紹了MySQL 用戶權限管理的相關資料，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下2021-04-04
Mysql分庫分表之后，主鍵如何處理？本文主要介紹了Mysql分庫分表之后主鍵處理的幾種方法，具有一定的參考價值，感興趣的可以了解一下2022-02-02
MySQL存儲引擎簡介及MyISAM和InnoDB的區(qū)別
這篇文章主要介紹了MySQL存儲引擎簡介及MyISAM和InnoDB的區(qū)別,重點介紹了MyISAM 和 InnoDB的區(qū)別,需要的朋友可以參考下2014-05-05