通过数据库编程进行递归（SQL Server）
一、用于测试数据及其结构和生成该测试数据的sql语句如下：
1、测试数据
nodeId parentId
---------- ----------
A01 A
A02 A
A03 A
A0101 A01
A0102 A01
A0201 A02
A0202 A02
B01 B
B02 B
B0201 B02
B0202 B02
B020101 B0201
B020102 B0201
2、建表及生成数据的sql语句
create table tree
(
nodeId varchar(50) not null,
parentId varchar(50) not null
);
insert into tree
select 'A01', 'A' union all
select 'A02', 'A' union all
select 'A03', 'A' union all
select 'A0101', 'A01' union all
select 'A0102', 'A01' union all
select 'A0201', 'A02' union all
select 'A0202', 'A02' union all
select 'B01', 'B' union all
select 'B02', 'B' union all
select 'B0201', 'B02' union all
select 'B0202', 'B02' union all
select 'B020101', 'B0201' union all
select 'B020102', 'B0201'
3、递归
3.1 sql server 2000 环境(由于sql server 2000不支持common table expression，因此需要写一函数来递归)
3.1.1 递归函数 [dbo].[func_get_children]
create function [dbo].[func_get_children](@parentId varchar(50))
returns @t table(parentId varchar(50), nodeId varchar(50), nodeLevel int)
as
begin
declare @i int
set @i = 1;
insert into @t(parentid, nodeId, nodeLevel)
select parentId, nodeId, @i
from tree
where parentId = @parentId;
while @@rowcount > 0
begin
set @i = @i + 1;
insert into @t(parentid, nodeId, nodeLevel)
select b.parentId, b.nodeId, @i
from @t a
inner join tree b on a.nodeId = b.parentId
where a.nodeLevel = @i - 1;
end
return;
end
3.1.2 调用该函数
select * from get_children('B');
3.1.3 执行结果
parentId nodeId nodeLevel
---------- ---------- -----------
B B01 1
B B02 1
B02 B0201 2
B02 B0202 2
B0201 B020101 3
B0201 B020102 3
3.2 sql server 2005 环境
3.2.1 递归sql
declare @parentId varchar(10)
set @parentId = 'b02';
with children as
(
select parentId, nodeId
from tree
where parentId = @parentId
union all
select a.parentId, a.nodeId
from tree a
inner join sons b on a.parentId = b.nodeId
)
select * from children;
3.2.2 执行结果
parentId nodeId
---------- ----------
B02 B0201
B02 B0202
B0201 B020101
B0201 B020102
