SQL Server進行遞迴查詢實現方法

以表Deparment為例

表結構為：

Id 部門內碼，

DeptCode 部門編碼，

ParentDeptId 上層業務內碼

使用T-SQL：

代碼如下	複製代碼
with Dep as (     select Id,DeptCode,DeptName from Department where Id=1     union all     select d.Id,d.DeptCode,d.DeptName from Dep     inner join Department d on dep.Id = d.ParentDeptId )

select * from Dep 簡單解釋一下：with as屬於sql server新特性CTE（Common Table Expression）的關鍵字，用來儲存臨時結果集。常用於代替子查詢。本例中可以理解為，找出Id=1的記錄後，存放在暫存資料表Dept中，然後暫存資料表和Department進行內串連，找出它的子記錄。子記錄和第一條記錄union後作為Dept新的結果集繼續進行內串連，找出新的子記錄。

使用PL/SQL：

代碼如下	複製代碼
select Id,DeptCode,DeptName from Department start with Id = 1 connect by prior Id = ParentDeptId;

 start with 表示從哪一行記錄開始遞迴查詢，即根節點
connect by 表示進行遞迴，後面跟遞迴條件
prior 表示前一條記錄，表示上一條記錄的Id = 下一條記錄的ParentDeptId
比如上面的SQL語句就可以理解為，以Id 為1的記錄為根節點，遞迴查詢下一條記錄的ParentDeptId = 前一條記錄的Id

執行個體

代碼如下	複製代碼
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   行） --*/