create table #temp
(id varchar(10))
declare @id varchar(10)
set @id = 'bsd'
insert into #temp values (@id)
while exists (select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp)select * from tempTab where tempTab.id = #temp.id
(id varchar(10))
declare @id varchar(10)
set @id = 'bsd'
insert into #temp values (@id)
while exists (select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp)select * from tempTab where tempTab.id = #temp.id
select * from temptab where id='bsd'
@id int
asdeclare @temp table (id int)insert @temp values (@id)while exists (
select id from tablename
where pid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from tablename
where pid in (select id from @temp)
and id not in (select id from @temp)while exists (
select id from tablename
where pid in (select id from @temp)
and pid not in (select id from @temp)
)
insert @temp
select pid from tablename
where id in (select id from @temp)
and pid not in (select id from @temp)
select * from tablename
where id in (select id from @temp)go
select * from temptab where id='bsd' --父节点
应该是
create proc proc_name
@id int
asdeclare @temp table (id int)insert @temp values (@id)while exists (
select id from tablename
where pid in (select id from @temp)
and id not in (select id from @temp)
)
insert @temp
select id from tablename
where pid in (select id from @temp)
and id not in (select id from @temp)
---把所有子节点插入@tempwhile exists (
select pid from tablename
where id in (select id from @temp)
and pid not in (select id from @temp)
)
insert @temp
select pid from tablename
where id in (select id from @temp)
and pid not in (select id from @temp)
---把所有父接点插入@tempselect * from tablename
where id in (select id from @temp)go
select identity(int,1,1) iid,*,cast('' as varchar(8000)) as NewCol into #temp from temTabdeclare @I int,@J int
select @I=1
select @j=(select max(iid) from #temp)
while @I<=@j
begin
update #temp
set NewCol=
isnull((select NewCol from #temp as cc where id=#temp.PId),',')+
','+#temp.id
where #temp.Iid=@I
select @i=@I+1
end
select * from #temp where NewCol like '%,bsd,%'
你到oracle坛子去问阿。
select * from #temp where NewCol like '%,bsd%' or iid=1
(id varchar(10))
declare @id varchar(10)
set @id = 'bsd'
insert into #temp values (@id)
while exists (select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tempTab where pid in (select * from #temp) and id not in (select * from #temp)select * from tempTab A, #temp B
where A.id = B.id