ID NAME FATHER_ID
1 test 0
2 a 1
3 b 1
4 a1 2
5 a2 2
6 b1 3
7 b2 3
8 a11 4
9 a12 4
10 a21 5
11 a22 5
12 b11 6
13 b12 6
14 b21 7
15 b22 7
有这样一个表,是实现TREEVIEW用的,我现在想实现一个功能:输入一个ID值,然后取出这个ID对应的所有子节点,这个ID做为根节点,如输入a的ID 2,就把 a,a1,a2,a11,a12,a21,a22检索出来,一个SQL语句能实现吗?
1 test 0
2 a 1
3 b 1
4 a1 2
5 a2 2
6 b1 3
7 b2 3
8 a11 4
9 a12 4
10 a21 5
11 a22 5
12 b11 6
13 b12 6
14 b21 7
15 b22 7
有这样一个表,是实现TREEVIEW用的,我现在想实现一个功能:输入一个ID值,然后取出这个ID对应的所有子节点,这个ID做为根节点,如输入a的ID 2,就把 a,a1,a2,a11,a12,a21,a22检索出来,一个SQL语句能实现吗?
drop table tbTree
if object_id('fnTree') is not null
drop function fnTree
GO
----创建测试数据
create table tbTree(ID int,NAME varchar(10),FATHER_ID int)
insert tbTree
select 1, 'test', 0 union all
select 2, 'a', 1 union all
select 3, 'b', 1 union all
select 4, 'a1', 2 union all
select 5, 'a2', 2 union all
select 6, 'b1', 3 union all
select 7, 'b2', 3 union all
select 8, 'a11', 4 union all
select 9, 'a12', 4 union all
select 10, 'a21', 5 union all
select 11, 'a22', 5 union all
select 12, 'b11', 6 union all
select 13, 'b12', 6 union all
select 14, 'b21', 7 union all
select 15, 'b22', 7
GO
----创建查找子节点的函数
create function fnTree (@ID int)
returns @t table(ID int,NAME varchar(10))
as
begin
insert @t select ID,NAME from tbTree where ID = @ID
while @@rowcount > 0
insert @t select a.ID,a.NAME from tbTree as a inner join @t as b on a.FATHER_ID = b.ID
and a.ID not in(select ID from @t)
return
end
GO
----查询节点
select * from fnTree(2)----清除测试环境
drop table tbTree
drop function fnTree/*结果
ID NAME
--------------------------
2 a
4 a1
5 a2
8 a11
9 a12
10 a21
11 a22
*/
from T1
where left(Name , (select Len(Name) from T1 where id = 输入ID)) = (select Name from T1 where id = 输入ID )
select 1, 'test', 0 union all
select 2, 'a', 1 union all
select 3, 'b', 1 union all
select 4, 'a1', 2 union all
select 5, 'a2', 2 union all
select 6, 'b1', 3 union all
select 7, 'b2', 3 union all
select 8, 'a11', 4 union all
select 9, 'a12', 4 union all
select 10,'a21', 5 union all
select 11, 'a22', 5 union all
select 12, 'b11', 6 union all
select 13, 'b12', 6 union all
select 14, 'b21', 7 union all
select 15, 'b22', 7
GO
select Name
from T1
where left(Name , (select Len(Name) from T1 where id = 3)) = (select Name from T1 where id = 3 )
/*结果
NAME
--------------------------
b
b1
b2
b11
b12
b21
b22*/
insert tbTree
select 1, 'test', 0 union all
select 2, 'a', 1 union all
select 3, 'b', 1 union all
select 4, 'a1', 2 union all
select 5, 'a2', 2 union all
select 6, 'b1', 3 union all
select 7, 'b2', 3 union all
select 8, 'a11', 4 union all
select 9, 'a12', 4 union all
select 10, 'a21', 5 union all
select 11, 'a22', 5 union all
select 12, 'b11', 6 union all
select 13, 'b12', 6 union all
select 14, 'b21', 7 union all
select 15, 'b22', 7create function test_f (@id int)
returns @ta table(ID int,NAME varchar(10),FATHER_ID int,lev int)
as
begin
declare @i int
set @i=0
insert @ta select *,@i from tbTree where id=@id
while @@rowcount>0
begin
set @i=@i+1
insert @ta
select a.*,@i
from tbTree a,@ta b
where b.id=a.FATHER_ID and b.lev=@i-1
end
return
end
select * from dbo.test_f(1)
ID NAME FATHER_ID lev
----------- ---------- ----------- -----------
1 test 0 0
2 a 1 1
3 b 1 1
4 a1 2 2
5 a2 2 2
6 b1 3 2
7 b2 3 2
8 a11 4 3
9 a12 4 3
10 a21 5 3
11 a22 5 3
12 b11 6 3
13 b12 6 3
14 b21 7 3
15 b22 7 3(所影响的行数为 15 行)
ID NAME FATHER_ID lev
----------- ---------- ----------- -----------
2 a 1 0
4 a1 2 1
5 a2 2 1
8 a11 4 2
9 a12 4 2
10 a21 5 2
11 a22 5 2(所影响的行数为 7 行)
insert tbTree
select 1, 'test', 0 union all
select 2, 'a', 1 union all
select 3, 'b', 1 union all
select 4, 'a1', 2 union all
select 5, 'a2', 2 union all
select 6, 'b1', 3 union all
select 7, 'b2', 3 union all
select 8, 'a11', 4 union all
select 9, 'a12', 4 union all
select 10, 'a21', 5 union all
select 11, 'a22', 5 union all
select 12, 'b11', 6 union all
select 13, 'b12', 6 union all
select 14, 'b21', 7 union all
select 15, 'b22', 7create proc test_p @id int
as
begin
declare @i int
set @i=0
select *,lev=@i into #
from tbTree where id=@id
while @@rowcount>0
begin
set @i=@i+1
insert #
select a.*,@i
from tbTree a,# b
where b.id=a.FATHER_ID and b.lev=@i-1
end
select name from #
end
测试:
exec test_p 2(所影响的行数为 1 行)
(所影响的行数为 2 行)
(所影响的行数为 4 行)
(所影响的行数为 0 行)name
----------
a1
a2
a11
a12
a21
a22
a(所影响的行数为 7 行)