求一条sql语句

[harryCom]

表1
Id
a1
a2
a3
a4
a5

表2
ParentId ChildId
a1 a2
a1 a3
a2 a4

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

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

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

[leohuang]

不属于输入节点到根结点的所有节点

啥意思?看不懂

[caixia615]

有点迷糊,关注,学习

[harryCom]

例如输入a4,a4的父节点是a2,a2的父节点是a1,在往上就没有节点了,所以从表1排除a4,a2,a1的就是a3和a5

或者输入a4,a2,a1也可以,最后可以not in一下

[harryCom]

上面写错了,或者输入a4,a2,a1也可以 -> 或者输出a4,a2,a1也可以

[rookie_one]

看差不多了，大致想这样吧
select ID from 表1
where ID<>'a2' and ID not in (select ParentId from 表2 where ID='a2')

[rookie_one]

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(输入值))

[rookie_one]

不对，正在改

[marco08]

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)

[jacobsan]

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

[rookie_one]

哎，还是没改出来，有待提高阿，赫赫

[harryCom]

问题已解决,谢谢各位