declare @idstr int,@lastlen in, select @idstr=@InputDelID ---如果要做成存储过程。这个就是传入参数 select @lastlen=0 while len(@idstr)<>@lastlen begin select @lastlen=len(@idstr) select @idstr=@idstr+','+cast(id as varchar) from table where fatherid in @idstr end update table where id in @idstr
表:table1declare @id int set @id=123 select * into #t from table1 where id=@id while exists(select 1 from table1 A,#t b where a.hiid=b.id and a.id not in (select id from #t)) insert #t select A.* from table1 A,#t b where a.hiid=b.id and a.id not in (select id from #t)update A set boolea=1 from table1 A where id in (select id from #t)
学j老师的: ID PID Sort_ID 1 0 1 2 0 2 3 2 3 4 1 2 5 1 1 6 2 1 7 2 2我想要的结果顺序为: ID PID Sort_ID 1 0 1 5 1 1 4 1 2 6 2 1 7 2 2 3 2 3declare @tmp1 table ([ID] int,PID int,Sort_ID int) insert @tmp1 select * from tab where [ID]=@id while exists(select 1 from tab a,@tmp1 b where a.pid=b.Sort_ID and a.Sort_ID not in (select Sort_ID from @tmp1)) insert @tmp1 select a.* from tab a,@tmp1 b where a.pid=b.Sort_ID and a.Sort_ID not in (select Sort_ID from @tmp1) select * from @tmp1 order by PID,Sort_ID
CREATE PROCEDURE p_set_status (declare @id int) AS create table #temp (id int) insert into #temp values (@id) while exists (select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp) ) insert into #temp select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp)update tree set status = 'ok' from #temp where tree.id = #temp.id drop table #temp
CREATE PROCEDURE p_set_status (declare @id int) AS create table #temp (id int) insert into #temp values (@id) while exists (select id from tree where hiid in (select * from #temp) and id not in (select * from #temp) ) insert into #temp select id from tree where hiid in (select * from #temp) and id not in (select * from #temp)update tree set state = '1' from #temp where tree.id = #temp.id drop table #temp
select @idstr=@InputDelID ---如果要做成存储过程。这个就是传入参数
select @lastlen=0
while len(@idstr)<>@lastlen
begin
select @lastlen=len(@idstr)
select @idstr=@idstr+','+cast(id as varchar) from table where fatherid in @idstr
end
update table where id in @idstr
set @id=123
select * into #t from table1 where id=@id
while exists(select 1 from table1 A,#t b where a.hiid=b.id and a.id not in (select id from #t))
insert #t select A.* from table1 A,#t b where a.hiid=b.id and a.id not in (select id from #t)update A set boolea=1 from table1 A where id in (select id from #t)
ID PID Sort_ID
1 0 1
2 0 2
3 2 3
4 1 2
5 1 1
6 2 1
7 2 2我想要的结果顺序为:
ID PID Sort_ID
1 0 1
5 1 1
4 1 2
6 2 1
7 2 2
3 2 3declare @tmp1 table ([ID] int,PID int,Sort_ID int)
insert @tmp1 select * from tab where [ID]=@id
while exists(select 1
from tab a,@tmp1 b
where a.pid=b.Sort_ID
and a.Sort_ID not in (select Sort_ID from @tmp1))
insert @tmp1 select a.*
from tab a,@tmp1 b
where a.pid=b.Sort_ID
and a.Sort_ID not in (select Sort_ID from @tmp1)
select * from @tmp1 order by PID,Sort_ID
create table #temp
(id int)
insert into #temp values (@id)
while exists (select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tree where fatherid in (select * from #temp) and id not in (select * from #temp)update tree set status = 'ok' from #temp where tree.id = #temp.id
drop table #temp
create table #temp
(id int)
insert into #temp values (@id)
while exists (select id from tree where hiid in (select * from #temp) and id not in (select * from #temp) )
insert into #temp select id from tree where hiid in (select * from #temp) and id not in (select * from #temp)update tree set state = '1' from #temp where tree.id = #temp.id
drop table #temp
有兴趣可以给我邮件
[email protected]