【Mysql】SQL高級技巧——遞歸用法及案例詳解

1. SQL遞歸概念:

?????????SQL遞歸查詢是一種用于處理具有層次結(jié)構(gòu)的數(shù)據(jù)的技術(shù)。它使用遞歸函數(shù)來遍歷樹形結(jié)構(gòu)，例如組織結(jié)構(gòu)、分類結(jié)構(gòu)等等。

????????遞歸查詢通常使用 "?WITH RECURSIVE "?語句實現(xiàn)。

????????WITH RECURSIVE 語句包含兩部分：
? ? ? ? ? ? a.遞歸部分: 定義了如何遞歸查詢數(shù)據(jù);
? ? ? ? ? ? b.終止條件部分: 定義了遞歸查詢何時停止。

2. SQL遞歸一般形式：

WITH RECURSIVE recursive_query_name (col1, col2, ..., coln) AS (
    -- 遞歸部分
    SELECT 
			initial_query_result_col1, 
			initial_query_result_col2, 
			...,
			initial_query_result_coln
    FROM initial_query
    UNION ALL
    SELECT 
			recursive_query_result_col1,
			recursive_query_result_col2, 
			..., 
			recursive_query_result_coln
    FROM recursive_query_name, recursive_query
    WHERE recursive_query_condition
)
-- 終止條件部分
SELECT * FROM recursive_query_name WHERE termination_condition;

????????在遞歸部分，我們先通過一個初始查詢(initial_query)得到一些初始的結(jié)果。然后我們通過UNION ALL運算將初始結(jié)果集合并到遞歸查詢結(jié)果中。接下來，在每次遞歸查詢中，我們使用前一次遞歸的結(jié)果(recursive_query_name)與遞歸查詢(recursive_query)進(jìn)行運算，并使用WHERE條件過濾掉不需要的數(shù)據(jù)。最后，在終止條件部分中，我們使用一個條件來判斷遞歸查詢何時停止。當(dāng)遞歸查詢到終止條件時，遞歸查詢結(jié)束，最終結(jié)果被返回。

3.?SQL遞歸優(yōu)缺點：

?優(yōu)點：

1. 靈活性：SQL遞歸查詢適用于各種類型的樹形結(jié)構(gòu)，而且可以根據(jù)具體的需要自定義遞歸查詢算法。
2. 可讀性：遞歸查詢通常比使用嵌套查詢或連接查詢更易于閱讀和理解。它可以用簡單的SQL語句來表示一個復(fù)雜的樹形結(jié)構(gòu)。
3. 便于維護(hù)：SQL遞歸查詢通常比其他方法更易于維護(hù)。例如，如果要更改樹形結(jié)構(gòu)中的某些節(jié)點，只需更改遞歸查詢算法即可。

缺點：

1. 性能：SQL遞歸查詢通常比其他方法慢。這是因為它需要進(jìn)行多次遞歸函數(shù)調(diào)用，并且可能需要訪問大量的數(shù)據(jù)。如果不正確地編寫遞歸查詢算法，還可能會導(dǎo)致死循環(huán)等問題，從而影響性能。
2. 復(fù)雜性：遞歸查詢算法通常比其他方法更復(fù)雜。如果不熟悉遞歸算法，編寫正確的遞歸查詢算法可能很困難。
3. 可伸縮性：SQL遞歸查詢不適合處理大型數(shù)據(jù)集。當(dāng)數(shù)據(jù)集變得太大時，查詢可能會變得非常緩慢，甚至無法運行。

總體而言，SQL遞歸查詢是一種非常有用的技術(shù)，可以處理樹形結(jié)構(gòu)的數(shù)據(jù)。雖然它具有一些缺點，但在正確使用的情況下，它仍然是一種非常強大和靈活的工具。

4.案例：公司部門關(guān)系遞歸查詢

? ?a.按DDL建表：

CREATE TABLE company_department (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    parent_department_id INT REFERENCES company_department(department_id)
);

? ?b.插入數(shù)據(jù)：

INSERT INTO company_department 
    (department_id, department_name, parent_department_id)
VALUES
    (1, '公司', NULL),
    (2, '人力資源部', 1),
    (3, '財務(wù)部', 1),
    (4, '市場部', 1),
    (5, '技術(shù)部', 1),
    (6, '招聘部', 2),
    (7, '薪資部', 2),
    (8, '成本控制部', 3),
    (9, '收支管理部', 3),
    (10, '品牌推廣部', 4),
    (11, '銷售部', 4),
    (12, '前端開發(fā)部', 5),
    (13, '后端開發(fā)部', 5)

? ?c.遞歸查詢公司部門關(guān)系SQL語句

WITH RECURSIVE department_tree (department_id, department_name, parent_department_id, depth, path) AS (
		SELECT 
			department_id, 
			department_name, 
			parent_department_id, 
			1 AS depth, 
			CAST(department_id AS CHAR(200)) AS path
		FROM company_department
		WHERE parent_department_id IS NULL
		UNION ALL
		SELECT 
			cd.department_id, 
			cd.department_name, 
			cd.parent_department_id, 
			dt.depth + 1 AS depth, 
			CONCAT(dt.path, ',', cd.department_id) AS path
		FROM company_department cd
			JOIN department_tree dt ON cd.parent_department_id = dt.department_id
	)
SELECT 
	department_id, department_name, parent_department_id, depth, path
FROM department_tree
ORDER BY path;

? ?d.sql案例詳解：

這個查詢使用了遞歸公共表達(dá)式來遍歷公司部門關(guān)系。公共表達(dá)式使用了兩個 SELECT 語句：

第一個 SELECT 語句選取了所有沒有父部門的根部門，并將它們添加到臨時表 department_tree 中。它們的深度被初始化為 1，并且它們的路徑被設(shè)置為它們的部門 ID。這個 SELECT 語句是遞歸查詢的起點。

第二個 SELECT 語句連接了 company_department 表和 department_tree 表。它選取了 company_department 表中所有具有父部門的部門，并連接到 department_tree 表中已經(jīng)存在的部門。對于每個連接的行，它們的深度是父部門的深度加 1，并且它們的路徑是父部門的路徑加上逗號和它們自己的部門 ID。

查詢返回了 department_tree 表中所有的部門，按照它們的路徑排序。這個排序方法使得在結(jié)果集中，每個部門都在它們的父部門之后，并且它們的順序是深度優(yōu)先遍歷的順序。????????

e.查詢結(jié)果截圖：

文章來源地址http://www.zghlxwxcb.cn/news/detail-638710.html

到了這里，關(guān)于【Mysql】一篇學(xué)會SQL中的遞歸的用法的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！