sql 函式實現三種父子遞迴

在實際運用中經常會建立這樣的結構表Category(Id, ParentId, Name)，特別是用於樹形結構時（選單樹，許可權樹..），這種表設計自然而然地會用到遞迴，若是在程式中進行遞迴（雖然在程式中遞迴真的更方便一些），無論是通過ADO.NET簡單sql查詢還是ORM屬性關聯都會執行多次sql語句，難免會造成一些效能上的損耗，所以乾脆使用sql的函式來解決這個問題，用函式返回我們最終需要的結果。

針對這類需求，這裡我列出三種常用的遞迴：

1. 以一個節點為基點，列出所有子節點直到無子 （找下級） 。這有點兒像點兵點將，主帥只有一個，下面是左將、右將，左將下面又有千夫長、百夫長，點兵時主帥下令集合，下面的將軍只管各自的隊伍。
2. 以一個節點為基點，列出所有父節點直到祖先（找上級） 。
3. 麵包屑導航資料（單條資料）

下面我以一幅圖列出這三種形式（實線表現的是我們最終想要的資料，第三幅圖中只有一條資料）：

OK，現在讓我們來實現這幾個需求，step by step。

1. 資料準備

根據上面的圖中的資料建立表結構和測試資料

create table Region
(
Id int identity primary key,
Name nvarchar(20),
ParentId int 
)
go
insert into Region(Name,ParentId) values('廣東',NULL)
insert into Region(Name,ParentId) values('深圳',1)
insert into Region(Name,ParentId) values('惠州',1)
insert into Region(Name,ParentId) values('羅湖區',2)
insert into Region(Name,ParentId) values('福田區',2)
insert into Region(Name,ParentId) values('龍崗區',2)
insert into Region(Name,ParentId) values('惠陽區',3)
insert into Region(Name,ParentId) values('龍門縣',3)
insert into Region(Name,ParentId) values('華強北',5)
insert into Region(Name,ParentId) values('體育館',5)

select * from Region

2. 正向遞迴實現

/*
 * summary:遞迴獲取所有子節點
*/
alter function GetRecursiveChildren
(
@Id int
)
returns @t table(Id int,ParentId int,Name nvarchar(20), [Level] int)
begin
    declare @i int
    set @i = 1
    --根節點，Level = 0
    insert into @t select @Id,@id,(select Name from Region where Id = @id),0
    --直屬子節點，Level = 1
    insert into @t select Id,ParentId,Name,@i from Region where ParentId = @Id
    
    --如果沒有新的值插入，迴圈結束
    while @@rowcount<>0
    begin
        set @i = @i + 1;
        insert into @t
        select 
            a.Id,a.ParentId,a.Name,@i
        from
            Region a, @t b
        where
            a.ParentId = b.Id and b.Level = @i - 1
    end
    return
end
go
--呼叫函式
select * from GetRecursiveChildren(2)

執行上面的函式得到如下圖的結果：

-----------------------------------------------------------------------------------------------------------------------------

當然自sql 2005後微軟提供了CTE（公用表表示式）也可以用於遞迴查詢，請參閱使用公用表示式的遞迴查詢。

上面的遞迴用CTE的sql程式碼如下：

declare @id int
set @id = 2
;with t as--如果CTE前面有語句，需要用分號隔斷
(
select Id, ParentId, Name
from Region
where Id = @id
union all
select r1.Id,r1.ParentId,r1.Name
from Region r1 join t as r2 on r1.ParentId = r2.Id
)
select * from t order by Id

3. 逆向遞迴實現

create function GetRecursiveParent
(
@Id int
)
returns @t table(Id int,ParentId int,Name nvarchar(20), [Level] int)
as
begin
    declare @i int
    set @i = 1
    --插入末節點，Level = 0
    insert into @t select @Id,@id,(select Name from Region where Id = @id),0
    --插入末節點的父節點，Level = 1
    insert into @t select Id,ParentId,Name,@i from Region 
    where Id = (select ParentId from Region where Id = @Id)
    --如果沒有新的值插入，迴圈結束
    while @@rowcount<>0
    begin
        set @i = @i + 1;
        insert into @t
        select 
            a.Id,a.ParentId,a.Name,@i
        from
            Region a, @t b
        where
            a.Id = b.ParentId and b.Level = @i - 1
    end
    return    
end
go
--呼叫函式
select * from GetRecursiveParent(10)
go

執行這個函式得到的結果如下：

4. 麵包屑實現

create function GetLevel
(
@Id int
)
returns @level table(IdLevel varchar(100),NameLevel nvarchar(200))
as
begin
    declare @IdLevel varchar(100),@NameLevel nvarchar(200),@Name nvarchar(50)
    select @IdLevel = cast(@Id as varchar(10))
    select @NameLevel = (select Name from Region where Id = @Id)
    
    while(exists(select Id,ParentId from Region where Id = (select ParentId from Region where Id = @Id)))
    begin
        select @Id = Id,@Name = Name from Region where Id = (select ParentId from Region where Id = @Id)
        select @IdLevel = cast(@Id as varchar(10)) + '>' + @IdLevel
        select @NameLevel = @Name + '>' + @NameLevel
    end
    insert into @level select @IdLevel,@NameLevel
    return
end
go
--呼叫函式
select * from GetLevel(10)
go

呼叫這個函式的結果如下：

本文sql原始碼下載：http://files.cnblogs.com/keepfool/region_recursive.zip