已知表一,求表二表一
PersonID Name FatherID
1 A 5
2 B 5
3 C 4
4 D 6
5 E NULL
6 F 5
7 G 2
8 H 8表二
PersonID Name FatherID lev
1 A 5 5,1
2 B 5 5,2
3 C 4 5,6,4,3
4 D 6 5,6,4
5 E NULL 5
6 F 5 5,6
7 G 2 5,2,7
8 H 9 9,8
9 i null 8
PersonID Name FatherID
1 A 5
2 B 5
3 C 4
4 D 6
5 E NULL
6 F 5
7 G 2
8 H 8表二
PersonID Name FatherID lev
1 A 5 5,1
2 B 5 5,2
3 C 4 5,6,4,3
4 D 6 5,6,4
5 E NULL 5
6 F 5 5,6
7 G 2 5,2,7
8 H 9 9,8
9 i null 8
create table tb_bookType(id int,typeName varchar(10),parentid int)
insert tb_bookType
select 1,'英语',0 union all
select 2,'生物',0 union all
select 3,'计算机',0 union all
select 4,'口语',1 union all
select 5,'听力',1 union all
select 6,'数据库',3 union all
select 7,'软件工程',3 union all
select 8,'SQL Server',6
gocreate function f_getP(@id int)
returns varchar(100)
as
begin
declare @re table(id int,level int)
declare @ret varchar(100)
declare @l int
set @l=0
insert @re select @id,@l
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.parentid,@l from tb_bookType a,@re b
where a.id=b.id and b.level=@l-1 and a.parentid<>0
end
update @re set level=@l-level
set @ret = ''
select @ret = @ret + ltrim(id) + ',' from @re order by level desc
return reverse(left(@ret,len(@ret)-1))
end
go select *,dbo.f_getP(id) lu
from tb_bookTypedrop function f_getP
drop table tb_bookType/****************id typeName parentid lu
----------- ---------- ----------- ----------------------------------------------------------------------------------------------------
1 英语 0 1
2 生物 0 2
3 计算机 0 3
4 口语 1 1,4
5 听力 1 1,5
6 数据库 3 3,6
7 软件工程 3 3,7
8 SQL Server 6 3,6,8(8 行受影响)