declare @user table (typeid int,parentid int,content varchar(200)) insert @user values(1,1,'a') insert @user values(2,1,'b') insert @user values(3,2,'c') insert @user values(5,5,'aa') insert @user values(6,5,'bb') insert @user values(7,5,'cc') declare @tmp1 table (a int,b int,c varchar(200))insert @tmp1 select * from @user where typeid=1 while exists(select 1 from @user a,@tmp1 b where a.parentid=b.a and a.typeid not in (select a from @tmp1)) insert @tmp1 select a.* from @user a,@tmp1 b where a.parentid=b.a and a.typeid not in (select a from @tmp1)select * from @tmp1
insert @user values(1,1,'a')
insert @user values(2,1,'b')
insert @user values(3,2,'c')
insert @user values(5,5,'aa')
insert @user values(6,5,'bb')
insert @user values(7,5,'cc')
declare @tmp1 table (a int,b int,c varchar(200))insert @tmp1 select * from @user where typeid=1
while exists(select 1 from @user a,@tmp1 b where a.parentid=b.a and a.typeid not in (select a from @tmp1))
insert @tmp1 select a.* from @user a,@tmp1 b where a.parentid=b.a and a.typeid not in (select a from @tmp1)select * from @tmp1