SQL數據庫實現遞歸查詢的幾種代碼方法

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   行）