有一表TB,内容如下:
子 父
123 456
456 789
012 234也就是说123的父节点是456,456的父节点789.012的父节点是234
我想得到这样的结果: 子 父
789 456
789 789
234 234
也就是把子列的值更改为最终父节点的值.
谢谢!
子 父
123 456
456 789
012 234也就是说123的父节点是456,456的父节点789.012的父节点是234
我想得到这样的结果: 子 父
789 456
789 789
234 234
也就是把子列的值更改为最终父节点的值.
谢谢!
with cte as
(
select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
union all
select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pid
go
create table [tb]([id] varchar(3),[pid] int)
insert [tb]
select '123',456 union all
select '456',789 union all
select '012',234
gowith cte as
(
select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
union all
select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pidselect * from [tb]
--测试结果:
/*
id pid
---- -----------
789 456
789 789
234 234(3 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] varchar(3),[pid] varchar(3))
insert [tb]
select '123','456' union all
select '456','789' union all
select '012','234'
gowith cte as
(
select topid=pid,id,pid from tb a where not exists(select 1 from tb where id=a.pid)
union all
select c.topid,a.id,a.pid from tb a join cte c on a.pid=c.id
)
update tb
set id=cte.topid
from cte
where tb.pid=cte.pidselect * from [tb]
--测试结果:
/*
id pid
---- -----------
789 456
789 789
234 234(3 行受影响)
*/
如果嵌套的层次不多的话,可以使用连接,直接写update a
set a.id=case when b.id is null then a.pid else b.pid end
from tb a left join tb b on b.pid=a.idselect * from tb456 456
456 789
234 234