SQL數據庫實現遞歸查詢的幾種代碼方法
表結構
ProductCategory
CategoryID,Level,ParentCategoryID
數據
1,1,-1
2,1,-1
3,2,1
4,3,3
5,2,2
6,4,5
T-SQL
WITH CategoryTemp(CategoryID,ParentCategoryID)--臨時表用來保存查到的Category
(
SELECT CategoryID,ParentCategoryID FROM ProductCategory WHERE ParentCategoryID<=0--將所有的第一層查出來作為初始數據，需要查第幾層或者哪個ParentCategoryID下面所有的 N層，把ParentCategoryID賦相關的值即可
UNION ALL--查詢N層
SELECT pc.CategoryID,ParentCategoryID FROM ProductCategory pc
LEFT JOIN CategoryTemp ct ON pc.ParentCategoryID=ct.CategoryID
WHERE ParentCategoryID>0--因為第一層前面已經查出來了，所以這裡把第一層篩選掉
)
SELECT CategoryID,ParentCategoryID FROM CategoryTemp
結果
1,-1
2,-1
3,1
4,3
5,2
6,5
如果把ParentCategoryID賦為2，結果則為
5,2
6,5
實例
ID 是否為部門 部門名 上級ID
1 y 部門0 1
31 y 部門1 1
32 n 張三 31
33 n 李二 31
34 y 部門2 31
35 n 王五 34
35 y 部門３ 34
36 n 小三 35
我想找詢 ID 值為 35 下級的所有人員包括下級部門的所有人員
--創建查詢函數
create function f_id(
@id int --要查詢的id
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select id,@l
from 表
where 上級id=@id
while @@rowcount> 0
begin
set @l=@l+1
insert @re select a.id,@l
from 表 a join @re b on a.上級id=b.id and b.level=@l-1
end
return
end
go
--調用函數進行查詢
select a.* from 表 a join f_id(35) b on a.id=b.id
聯合查詢
--測試數據
create table 表(ID int,是否為部門 char(1),部門名 varchar(10),上級ID int)
insert 表 select 1 , 'y ', '部門0 ' ,1
union all select 31, 'y ', '部門1 ' ,1
union all select 32, 'n ', '張三 ' ,31
union all select 33, 'n ', '李二 ' ,31
union all select 34, 'y ', '部門2 ',31
union all select 35, 'n ', '王五 ' ,34
union all select 35, 'y ', '部門3 ',34
union all select 36, 'n ', '小三 ' ,35
go
--創建查詢函數
create function f_id(
@id int --要查詢的id
)returns @re table(id int,level int)
as
begin
declare @l int
set @l=0
insert @re select id,@l
from 表
where 上級id=@id
while @@rowcount> 0
begin
set @l=@l+1
insert @re select a.id,@l
from 表 a join @re b on a.上級id=b.id and b.level=@l-1
end
return
end
go
--調用函數進行查詢
select a.* from 表 a join f_id(35) b on a.id=b.id
go
--刪除測試
drop table 表
drop function f_id
/*--測試結果
ID 是否為部門 部門名 上級ID
----------- ----- ---------- -----------
36 n 小三 35
（所影響的行數為 1 行）
