ID LID RID
1 1 2
8 3 4
3 5 6
6 7 8
ID LID RID
1 5 6
8 7 8
3 1 2
6 3 4前辈们,能不通过临时表写出上面的SQL语句吗?
顺序是由LID和RID排成的
参考图(这个图是无限分类解释图,原理差不多):
我的写法是这样,用了临时表declare @movID int --移动ID
set @movID=3
DECLARE @movLID AS INT, @movRID AS INT, @movLen AS INT,@movTree AS INT,@preID AS INT,@preLID AS INT,@preRID AS INT,@preLen AS INT,@preTree AS INT
IF @movID > 0
BEGIN
select @movLID=LID,@movRID=RID,@movTree=Tree from CL_ZoneData where ID=@movIDset @preRID=@movLID-1
select @preID=ID,@preLID=LID,@preTree=Tree from CL_ZoneData where RID=@preRID
IF @preTree = @movTree
BEGIN
select ID into #preTempTable from CL_ZoneData where LID between @preLID and @preRID --preTempTableset @preLen=@preRID - @preLID+1
set @movLen=@movRID - @movLID+1update CL_ZoneData set LID=LID-@preLen,RID=RID-@preLen where ID in (select ID from CL_ZoneData where RID between @movLID and @movRID )
update CL_ZoneData set LID=LID+@movLen,RID=RID+@movLen where ID in (select ID from #preTempTable)drop table #preTempTable END
END
不用临时表的简单方法
create table t
(
id int,
lid int,
rid int
)
insert t select 1,1,2
insert t select 8,3,4
insert t select 3,5,6
insert t select 6,7,8begin tran
update t set lid = tbl.lid,rid = tbl.rid
from
(
select * from t
)tbl
where case when t.lid<5 then t.lid+4 else t.lid-4 end = tbl.lid
--id=3没办法标示,只好拿lid<5了
commitselect * from t