SQL实现递归

--生成测试数据
create table Dept(ID int,ParentID int,msg varchar(20))
insert into Dept select 1,0,null
insert into Dept select 2,0,null
insert into Dept select 3,1,null
insert into Dept select 4,1,null
insert into Dept select 5,3,null
insert into Dept select 6,3,null
insert into Dept select 7,4,null
go

--方法1:在SQL Server2005中提供了CTE[公共表表达式]来实现递归。
Declare @Id Int
Set @Id = 0;  --在此修改父节点

With RootNodeCTE(Id,ParentId)
As
(
Select Id,ParentId From Dept Where ParentId In (@Id)
Union All
Select Dept.Id,Dept.ParentId From RootNodeCTE
Inner Join Dept
On RootNodeCTE.Id = Dept.ParentId
)

Select * From RootNodeCTE
--------------------------------------------------------------------------------

--方法2:创建表值函数实现递归
Create function [dbo].[GetChild](@ID varchar(10))
  returns @t table(ID varchar(10),ParentID varchar(10),Level int)
as
begin
  declare @i int
  set @i = 1
 
  insert into @t select ID,ParentID,@i from Dept where ParentID = @ID

  while @@rowcount<>0
  begin
    set @i = @i + 1
    insert into @t select a.ID,a.ParentID,@i from Dept a,@t b
    where a.ParentID=b.ID and b.Level = @i-1
  end
 
  return
end
--------------------------------------------------------------------------------

--执行查询
select * from dbo.GetChild(0)

--删除测试数据
drop function GetChild
drop table Dept

备注：1.原理:先将上一级数据插入"临时表"，然后根据上一级数据找到它的下一级数据插入"临时表",如此往复....; 2.@@rowcount:返回受上一语句影响的行数;