前言

在 MySQL 中，WITH 語句（或稱為公用表表達(dá)式，Common Table Expressions，簡稱 CTE）用于定義一個臨時(shí)結(jié)果集，可以在查詢的其他部分中重復(fù)引用。通常用在復(fù)雜查詢中，方便將查詢邏輯分解為多個部分，代碼更清晰，并且可以重復(fù)使用中間結(jié)果。

MySQL 支持兩種類型的 CTE：

• 非遞歸 CTE：基本的 WITH 語句，用于定義一次性計(jì)算的結(jié)果集。
• 遞歸 CTE：CTE 自己引用自己，通常用于分層數(shù)據(jù)或樹狀結(jié)構(gòu)的查詢。

下面分別介紹它們的用法和一些常見示例。

1. 非遞歸 CTE

非遞歸 CTE 在查詢中定義一個固定的結(jié)果集，在執(zhí)行后不會再改變。語法如下：

WITH cte_name AS ( SELECT ... ) SELECT * FROM cte_name;

示例 1：計(jì)算部門員工的平均工資

假設(shè)有一個 employees 表，包含員工的 department_id、name 和 salary。

WITH dept_avg_salary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg_salary d ON e.department_id = d.department_id WHERE e.salary > d.avg_salary;

這個查詢首先用 WITH 計(jì)算各部門的平均工資（dept_avg_salary），然后找出工資高于部門平均工資的員工。

示例 2：按條件拆分查詢

假設(shè)要找到銷售額最高的 5 位銷售人員，可以使用 CTE 進(jìn)行臨時(shí)排名：

WITH ranked_sales AS ( SELECT name, sales_amount, ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS rank FROM sales_team ) SELECT name, sales_amount FROM ranked_sales WHERE rank <= 5;

CTE ranked_sales 生成了一個帶排名的銷售記錄表，然后主查詢從中提取前五名。

2. 遞歸 CTE

遞歸 CTE 允許在定義時(shí)引用自身，常用于層級結(jié)構(gòu)的查詢，比如管理層次結(jié)構(gòu)、樹形結(jié)構(gòu)等。語法如下：

WITH RECURSIVE cte_name AS ( SELECT ... -- 初始查詢 UNION ALL SELECT ... FROM cte_name -- 遞歸查詢 ) SELECT * FROM cte_name;

示例 3：計(jì)算階乘

下面是一個遞歸 CTE 示例，計(jì)算 1 到 5 的階乘。

WITH RECURSIVE factorial_cte AS ( SELECT 1 AS n, 1 AS factorial UNION ALL SELECT n + 1, factorial * (n + 1) FROM factorial_cte WHERE n < 5 ) SELECT * FROM factorial_cte;

這個 CTE 首先定義了 n=1 和 factorial=1 的初始值，然后遞歸地計(jì)算 1 到 5 的階乘。

示例 4：查詢部門的層級結(jié)構(gòu)

假設(shè)有一個 departments 表，每個部門都有一個 id 和 parent_id（指向上級部門）。遞歸 CTE 可以查詢從某個部門開始的所有子部門。

WITH RECURSIVE dept_hierarchy AS ( SELECT id, name, parent_id FROM departments WHERE id = 1 -- 從根部門 ID 為 1 開始 UNION ALL SELECT d.id, d.name, d.parent_id FROM departments d JOIN dept_hierarchy h ON d.parent_id = h.id ) SELECT * FROM dept_hierarchy;

3. 嵌套 CTE 和多 CTE 定義

在一個查詢中可以定義多個 CTE，并在查詢的其他部分引用它們。這些 CTE 可以相互引用，按順序處理。

示例 5：多個 CTE 的嵌套查詢

假設(shè)要查詢一組數(shù)據(jù)的中間計(jì)算結(jié)果，可以使用嵌套 CTE：

WITH initial_sales AS ( SELECT salesperson_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY salesperson_id ), ranked_sales AS ( SELECT salesperson_id, total_sales, RANK() OVER (ORDER BY total_sales DESC) AS sales_rank FROM initial_sales ) SELECT salesperson_id, total_sales, sales_rank FROM ranked_sales WHERE sales_rank <= 10;

這里，initial_sales 計(jì)算每個銷售人員的總銷售額，ranked_sales 對銷售額進(jìn)行排名，然后主查詢獲取前十名銷售人員。

4. 使用 CTE 簡化復(fù)雜查詢邏輯

示例 6：復(fù)雜查詢的分步計(jì)算

假設(shè)有一個電商訂單系統(tǒng)，要求統(tǒng)計(jì)每月每個產(chǎn)品的銷售額及增長率。

WITH monthly_sales AS ( SELECT product_id, DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(sales_amount) AS total_sales FROM orders GROUP BY product_id, month ), sales_growth AS ( SELECT m1.product_id, m1.month, m1.total_sales, (m1.total_sales - m2.total_sales) / m2.total_sales AS growth_rate FROM monthly_sales m1 LEFT JOIN monthly_sales m2 ON m1.product_id = m2.product_id AND DATE_FORMAT(DATE_SUB(m1.month, INTERVAL 1 MONTH), '%Y-%m') = m2.month ) SELECT * FROM sales_growth;

這個查詢使用兩個 CTE：monthly_sales 計(jì)算每月的總銷售額，sales_growth 計(jì)算月銷售增長率。

總結(jié)

WITH 語句的多種用法總結(jié)如下：

• 非遞歸 CTE 用于分解復(fù)雜查詢。
• 遞歸 CTE 用于層級數(shù)據(jù)查詢。
• 嵌套 CTE 可以組合多個步驟的查詢。
• 簡化查詢邏輯：分解復(fù)雜的 SQL 邏輯，使查詢更清晰易懂。

CTE 是復(fù)雜查詢中不可或缺的工具，有助于使代碼簡潔且易于維護(hù)。

WITH RECURSIVE 舉例說明，表結(jié)構(gòu)是id和pid的指向 大概有五層

示例：遞歸查詢部門層級

假設(shè)有一個 departments 表，結(jié)構(gòu)如下：

• id: 部門 ID
• name: 部門名稱
• pid: 父級部門 ID（頂級部門的 pid 為 NULL）

表中有五層嵌套的部門數(shù)據(jù)：

遞歸 CTE 查詢：獲取指定部門的所有下級部門

我們可以使用遞歸 CTE 從根部門（例如公司層級的 id=1）開始，查詢所有子部門并顯示層級關(guān)系。

wITH RECURSIVE dept_hierarchy AS ( -- 初始查詢，獲取頂級部門（這里我們從 id=1 的公司開始） SELECT id, name, pid, 1 AS level FROM departments WHERE id = 1 -- 這里可以更改為要查詢的根部門的 ID UNION ALL -- 遞歸查詢：找到上級部門（父級）的下一級部門 SELECT d.id, d.name, d.pid, h.level + 1 AS level FROM departments d JOIN dept_hierarchy h ON d.pid = h.id ) SELECT * FROM dept_hierarchy; 

查詢結(jié)果解釋

這個遞歸 CTE 分為兩部分：

• 初始查詢：SELECT id, name, pid, 1 AS level，從指定的部門（id=1）開始，將其層級設(shè)為1。
• 遞歸查詢：從上級部門的 id（即 h.id）出發(fā)，查找其所有下級部門，并將 level 加 1，這樣層級關(guān)系會遞歸增長，直到?jīng)]有下級部門。

執(zhí)行后，結(jié)果顯示部門的層級關(guān)系：

在這個查詢中，level 列表示部門的層級，從1開始遞增。

總結(jié)

到此這篇關(guān)于mysql中WITH的多種用法與常見示例的文章就介紹到這了,更多相關(guān)mysql WITH用法示例內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論