表1
Id
a1
a2
a3
a4
a5表2
ParentId ChildId
a1 a2
a1 a3
a2 a4如果输入a2等到的结果是:
a3
a4
a5如果输入a4等到的结果是:
a3
a5目的就是找出不属于输入节点到根结点的所有节点
Id
a1
a2
a3
a4
a5表2
ParentId ChildId
a1 a2
a1 a3
a2 a4如果输入a2等到的结果是:
a3
a4
a5如果输入a4等到的结果是:
a3
a5目的就是找出不属于输入节点到根结点的所有节点
select ID from 表1
where ID<>'a2' and ID not in (select ParentId from 表2 where ID='a2')
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
endreturn @s2
endselect * from 表1 where ID not in(fun_name(输入值))
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
endselect * 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)
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+')')
--resa3
a5