求一条sql语句
表1
Id
a1
a2
a3
a4
a5

表2
ParentId     ChildId
a1                 a2
a1                 a3
a2                 a4

如果输入a2等到的结果是:
a3
a4
a5

如果输入a4等到的结果是:
a3
a5

目的就是找出不属于输入节点到根结点的所有节点


------解决方案--------------------
create function fun_name (@str varchar(10))
returns varchar(100)
as
begin
declare @s1 varchar(10)
declare @s2 varchar(10)

set @s1= ' '
set @s2= ' '
begin
if exists(select ParentId from 表2 where ChildId=@str)
select @s1=ParentId from 表2 where ChildId=@str
set @s2=@s2+ ', '+@s1
set @str=@s1
end

return @s2
end

select * from 表1 where ID not in(fun_name(输入值))
------解决方案--------------------
create table A(Id char(2))
insert A select 'a1 '
union all select 'a2 '
union all select 'a3 '
union all select 'a4 '
union all select 'a5 '

create table B(ParentId char(2), ChildId char(2))
insert B select 'a1 ', 'a2 '
union all select 'a1 ', 'a3 '
union all select 'a2 ', 'a4 '

create function f_pid(@ID char(2))
returns @t_level table(ID char(2), Level int)
as
begin
declare @level int
set @level=1

insert @t_level select @ID, @level
while @@rowcount> 0
begin
set @level=@level+1
insert @t_level select b.ParentId, @level
from B as b, @t_level as a
where b.ChildId=a.ID and a.Level=@level-1
end

return
end

select * from A where id not in
(
select ID from f_pid( 'a2 ')
)
--result
Id
----
a3
a4
a5

(3 row(s) affected)


select * from A where id not in
(
select ID from f_pid( 'a4 ')
)
--result
Id
----
a3
a5

(2 row(s) affected)
------解决方案--------------------
create table 表1(Id varchar(10))

insert into 表1 values( 'a1 ')
insert into 表1 values( 'a2 ')
insert into 表1 values( 'a3 ')
insert into 表1 values( 'a4 ')
insert into 表1 values( 'a5 ')


create table 表2(ParentId varchar(10), ChildId varchar(10))
insert into 表2 values( 'a1 ', 'a2 ')
insert into 表2 values( 'a1 ', 'a3 ')
insert into 表2 values( 'a2 ', 'a4 ')

declare @a varchar(10)
declare @b varchar(10)
set @a= 'a4 ' --这里输入结点
declare @res varchar(1000)
set @res= ' '
declare @per varchar(10)
set @per=@a
while @per is not null
begin
set @b=null
select @b=ParentId from 表2 where ChildId=@per
if @b is not null
set @res=@res+ ', ' ' '+@b+ ' ' ' '
set @per=@b
end
set @res=@res+ ', '+ ' ' ' '+@a+ ' ' ' '
set @res=stuff(@res,1,1, ' ')

exec( 'select * from 表1 where id not in( '+@res+ ') ')


--res

a3
a5