有示例數(shù)據(jù)，表名為SYS_Department

遞歸查詢原理

SQL Server中的遞歸查詢是通過CTE(表表達(dá)式)來實(shí)現(xiàn)。至少包含兩個查詢，第一個查詢?yōu)槎c(diǎn)成員，定點(diǎn)成員只是一個返回有效表的查詢，用于遞歸的基礎(chǔ)或定位點(diǎn)；第二個查詢被稱為遞歸成員，使該查詢稱為遞歸成員的是對CTE名稱的遞歸引用是觸發(fā)。在邏輯上可以將CTE名稱的內(nèi)部應(yīng)用理解為前一個查詢的結(jié)果集。

遞歸查詢語法

with 子查詢名稱 查詢字段列表 as --查詢字段列表輸入格式為([查詢字段名1],[查詢字段名2],...)，也可用*代替，需要注意的是此處的查詢字段列表中的字段與下方內(nèi)容中定點(diǎn)成員與遞歸成員查詢字段列表內(nèi)容需一致
( 
--定點(diǎn)成員
select 查詢字段列表 from 查詢表名 where 查詢條件 
union all 
--遞歸成員
select 查詢字段列表 from 查詢表名 a inner join 子查詢名稱 on 遞歸條件 ) 
select 顯示字段名 from 子查詢名稱

已示例數(shù)據(jù)為例

--查詢1
--通過根節(jié)點(diǎn)向下查找該根節(jié)點(diǎn)下的子節(jié)點(diǎn)
with temp([id],[departmentName],[parentId])  as --temp([id],[departmentName])指定temp結(jié)果集中的列，如設(shè)置需包含聯(lián)表?xiàng)l件
( select [id],[departmentName],[parentId] from SYS_Department where id = 2
union all select a.[id],a.[departmentName],a.[parentId] from SYS_Department a inner join temp on a.[parentId] = temp.[id] ) 
select  [id],[departmentName] from temp

--查詢2
--通過子節(jié)點(diǎn)向上查找該子節(jié)點(diǎn)的根節(jié)點(diǎn)
with temp([id],[departmentName],[parentId])  as 
( select [id],[departmentName],[parentId] from SYS_Department where id = 2
union all select a.[id],a.[departmentName],a.[parentId] from SYS_Department a inner join temp on
--調(diào)換查詢字段
a.[id] = temp.[parentId] ) 
select  [id],[departmentName] from temp

查詢2查詢結(jié)果

mysql示例 使用recursive關(guān)鍵字

--Mysql 查詢時聯(lián)表?xiàng)l件與SQL SERVER相反
--父查子
with recursive temp(Id,DepartmentName,ParentId) as 
(select Id,DepartmentName,ParentId from SYS_Department  where id = 2
union all 
select a.Id,a.DepartmentName,a.ParentId from SYS_Department a inner join temp on a.ParentId  = temp.Id)
select * from temp

--子查父
with recursive temp(Id,DepartmentName,ParentId) as 
(select Id,DepartmentName,ParentId from SYS_Department  where id = 2
union all 
select a.Id,a.DepartmentName,a.ParentId from SYS_Department a inner join temp on a.Id = temp.ParentId )
select * from temp

不指定查詢字段列表

--查詢1
--不指定子查詢查詢字段列表
with temp as 
(
--查詢表中所有字段 
select * from SYS_Department where id = 2
union all 
--查詢主表中所有字段（根據(jù)UNION ALL語法需與定點(diǎn)成員查詢字段保持一致）
select a.* from SYS_Department a inner join temp on a.[parentId] = temp.[id] ) 
--顯示表中所有字段
select * from temp

--查詢2
--不指定子查詢查詢字段列表
with temp as 
(
--查詢表中所有字段 
select * from SYS_Department where id = 2
union all 
--查詢主表中所有字段（根據(jù)UNION ALL語法需與定點(diǎn)成員查詢字段保持一致）
select a.* from SYS_Department a inner join temp on a.[parentId] = temp.[id] ) 
--顯示表中id字段
select id from temp

查詢結(jié)果1

查詢結(jié)果2

 總結(jié)：

以樹的概念去理解查詢原理可能相對比較容易一些，以下是以結(jié)果反推的個人理解：

關(guān)鍵字：

定點(diǎn)成員（示例語句中：select * from SYS_Department where id = 2）

遞歸成員（示例語句中：select a.* from SYS_Department a inner join temp on a.[parentId] = temp.[id]）

拼接（使用UNION ALL）

SQL遞歸查詢是以定點(diǎn)成員為查詢起始點(diǎn)（即語句中的select * from SYS_Department where id = 2），第一次查詢時取到定點(diǎn)成員的子節(jié)點(diǎn)（即示例數(shù)據(jù)中parentid=2的數(shù)據(jù)，可以將查詢起始點(diǎn)理解為樹的根節(jié)點(diǎn)），通過特定的順序遍歷取到的子節(jié)點(diǎn)（遍歷順序經(jīng)測試為倒序遍歷，即先遍歷id=4的數(shù)據(jù)，再遍歷id=3的數(shù)據(jù)），當(dāng)該子節(jié)點(diǎn)下存在數(shù)據(jù)時（即已select a.* from SYS_Department a inner join temp on a.[parentId] = temp.[id]的查詢結(jié)果不為NULL時）顯示數(shù)據(jù)并繼續(xù)遍歷該節(jié)點(diǎn)，直至查詢導(dǎo)的結(jié)果為空或達(dá)到最大的迭代次數(shù)（默認(rèn)為100），最終將所有節(jié)點(diǎn)遍歷完成并將遍歷的結(jié)果以UNION ALL語句拼接輸出結(jié)果集。

以示例數(shù)據(jù)為例，反推的查詢流程以及數(shù)據(jù)如圖所示：

 1.第一次遞歸時（目前無法確定開始執(zhí)行的順序是先執(zhí)行定點(diǎn)成員語句后遞歸還是獲取到子節(jié)點(diǎn)后遞歸，猜測順序?yàn)閳?zhí)行完定點(diǎn)成員SQL后進(jìn)行第一次遞歸）獲取拼接下圖中

2.遞歸遍歷通過定點(diǎn)成員獲取到的子節(jié)點(diǎn)，先遍歷id=11的節(jié)點(diǎn)，但該節(jié)點(diǎn)下獲取的數(shù)據(jù)為空所以不拼接任何數(shù)據(jù)，id=10和9的同理，當(dāng)遍歷到id為4的節(jié)點(diǎn)時，獲取拼接下圖數(shù)據(jù)

 3.遞歸遍歷上圖中獲取到的節(jié)點(diǎn)，先遍歷id=8的節(jié)點(diǎn)，但該節(jié)點(diǎn)下獲取的數(shù)據(jù)為空所以不拼接任何數(shù)據(jù)，id=7和5的同理，當(dāng)遍歷到id為6的節(jié)點(diǎn)時，獲取拼接下圖數(shù)據(jù)

 4.遞歸遍歷上圖中獲取到的節(jié)點(diǎn)，先遍歷id=15的節(jié)點(diǎn)，但該節(jié)點(diǎn)下獲取的數(shù)據(jù)為空所以不拼接任何數(shù)據(jù)，id=14，13，12的節(jié)點(diǎn)同理

5.此時以id=4的節(jié)點(diǎn)為根節(jié)點(diǎn)的所有節(jié)點(diǎn)已遍歷完成，繼續(xù)遞歸遍歷通過定點(diǎn)成員獲取到的子節(jié)點(diǎn)，即遍歷id=3的節(jié)點(diǎn)，但該節(jié)點(diǎn)下獲取的數(shù)據(jù)為空所以不拼接任何數(shù)據(jù)，最終顯示結(jié)果集如下圖所示

 原文參考：

WITH common_table_expression (Transact-SQL) | Microsoft Docs

Recursive Queries Using Common Table Expressions | Microsoft Docs

思路借鑒：SQL中的遞歸的用法_SQL數(shù)據(jù)庫開發(fā)的博客-CSDN博客

到此這篇關(guān)于SQL SERVER遞歸查詢的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)SQL 遞歸查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論