SQL SERVER遞歸查詢的實現(xiàn)

更新時間：2024年01月03日 11:19:07 作者：MoFe1

本文主要介紹了SQL SERVER遞歸查詢的實現(xiàn),遞歸查詢是通過CTE來實現(xiàn),具有一定的參考價值,感興趣的可以了解一下

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

id	departmentName	parentId	remark	isEnable	staffId
2	總經(jīng)理	1	總經(jīng)理	1
3	賬務部	2	賬務部	1	1006
4	技術部	2	技術部	1	1004
9	產(chǎn)品部	2	產(chǎn)品部	1	1005
10	市場部	2	市場部	1
11	人事部	2	人事部	1	1013
5	測試部	4	測試部	1	1002
6	開發(fā)部	4	開發(fā)部	1	1003
7	設計部	4	設計部	1
8	物聯(lián)部	4	物聯(lián)部	1
12	JAVA一部	6	JAVA一部	1	1009
13	JAVA二部	6	JAVA二部	1
14	.NET部	6	.NET部	1	1008
15	前端部	6	前端部	1

遞歸查詢原理

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

遞歸查詢語法

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

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

--查詢1
--通過根節(jié)點向下查找該根節(jié)點下的子節(jié)點
with temp([id],[departmentName],[parentId])  as --temp([id],[departmentName])指定temp結果集中的列，如設置需包含聯(lián)表條件
( 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é)點向上查找該子節(jié)點的根節(jié)點
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查詢結果

mysql示例使用recursive關鍵字

--Mysql 查詢時聯(lián)表條件與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語法需與定點成員查詢字段保持一致）
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語法需與定點成員查詢字段保持一致）
select a.* from SYS_Department a inner join temp on a.[parentId] = temp.[id] ) 
--顯示表中id字段
select id from temp

查詢結果1

查詢結果2

總結：

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

關鍵字：

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

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

拼接（使用UNION ALL）

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

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

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