数据库的结构是
id patentID
1 0
2 1
3 2
4 3
5 4
6 5
7 6
8 0
9 8
10 0
11 8
12 9
……
当只知道id为某一值或某几值时,怎么能够查出给出id的所有父级和他们的子级
如:id=(3,4)时
怎么写能查出
id patentID
1 0
2 1
3 2
4 3
5 4
6 5
7 6
id patentID
1 0
2 1
3 2
4 3
5 4
6 5
7 6
8 0
9 8
10 0
11 8
12 9
……
当只知道id为某一值或某几值时,怎么能够查出给出id的所有父级和他们的子级
如:id=(3,4)时
怎么写能查出
id patentID
1 0
2 1
3 2
4 3
5 4
6 5
7 6
declare @id int
declare @tmp table (id int,lx int default 0)
declare @resulttmp table (id int,parentid int)--用于保存结束
set @id=4 --在此赋要查的id值
insert into @resulttmp select id,parentid from a where id=@id
insert into @tmp(id) select parentid from a where id=@id
--获得父记录
while exists(select * from a,@tmp b where a.id=b.id and b.lx=0)
begin
insert into @resulttmp
select id,parentid
from a where id in (select id from @tmp where lx=0)
insert into @tmp (id,lx) select parentid,1 from a where id in (select id from @tmp where lx=0)
delete @tmp where lx=0
update @tmp set lx=0
end--获得子记录
delete @tmp
insert into @tmp (id) values (@id)
while exists(select * from a,@tmp b where a.parentid=b.id and b.lx=0)
begin
insert into @resulttmp (id,parentid)
select id,parentid from a where parentid in (select id from @tmp where lx=0)
insert into @tmp (id,lx) select id,1 from a where parentid in (select id from @tmp where lx=0)
delete @tmp where lx=0
update @tmp set lx=0
endselect * from @resulttmp --所有父与子记录