--测试数据
declare @t table(id int,层次 int,parent_id int)
insert into @t(id,层次)
select 1,1
union all select 2,2
union all select 3,2
union all select 4,3
union all select 5,4
union all select 6,3
--更新处理
update @t set parent_id=case 层次 when 1 then 0
else (select top 1 id from @t where 层次=a.层次-1 and id<a.id order by id desc) end
from @t a
理解樓主的意思了。已經測試成功
update tbl set parent_id = case when (select top 1 id from tbl b where b.層次 = tbl.層次 -1 and b.id < tbl.id order by b.id desc) is null then 0
else (select top 1 id from tbl b where b.層次 = tbl.層次 -1 and b.id < tbl.id order by b.id desc) end
肯定可行的。
我用:
select * from tbl a
where exists (select top 1 b.id from tbl b where b.id<a.id and b.层次=a.层次-1 order by b.xh_id desc) 可以看出正确选择出了记录,
但是select * from tbl a
where a.id = (select top 1 b.id from tbl b where b.id<a.id and b.层次=a.层次-1 order by b.xh_id desc) 为什么就没有记录呢?