sql - 递归 CTE T- SQL 链中的最低值

Question

这是摆好的桌子。

create table ids 
(
     id int not null, 
     new_id int not null
)
GO

insert ids (id, new_id) values(290, 190)
insert ids (id, new_id) values(390, 190)
insert ids (id, new_id) values(490, 190)
insert ids (id, new_id) values(580, 280)
insert ids (id, new_id) values(680, 280)
insert ids (id, new_id) values(280, 150)
insert ids (id, new_id) values(780, 150)
insert ids (id, new_id) values(990, 620)
insert ids (id, new_id) values(150, 140)
GO

数据

id  new_id
-----------
290 190
390 190
490 190
580 280
680 280
280 150
780 150
990 620
150 140

id 被解析为新的 id，新的 id 总是低于初始 id。

我想要 ids 和他们最低的 new_ids。

到目前为止，我的尝试是这样的。

with cte_ids as
(
    select
        a.id,
        a.new_id
    from
        ids a
    left join
        ids b on a.new_id = b.id
    where 
        b.id is null

    union all

    select
        c.id,
        c.new_id
    from
        ids  c
    join 
        cte_ids d on d.id = c.new_id
)
select * from cte_ids

哪个返回结果

id  new_id
------------
290 190
390 190
490 190
990 620
150 140
280 150
780 150
580 280
680 280

结果不正确，因为 id 580 应该是 140，因为 580 变成了 280，然后 280 变成了 150，然后 150 变成了 140。

预期的结果是

id  new_id
------------
290 190
390 190
490 190
580 140
680 140
280 140
780 140
990 620
150 140

如何得到这个结果？

标签： sqlsql-servertsqlhierarchical-datarecursive-query

Answer 1

据我了解您的问题，您希望遵循id原始表中每个的关系，并带来最新的可用new_id.

考虑：

with cte_ids as (
    select id, new_id, 1 lvl from ids
    union all
    select c.id, i.new_id, c.lvl + 1
    from cte_ids c 
    inner join ids i on i.id = c.new_id
)
select id, new_id
from cte_ids c
where c.lvl = (select max(c1.lvl) from cte_ids c1 where c1.id = c.id)
order by id

理由：

• 递归查询的anchor select all ids

• 然后递归部分遵循关系，同时跟踪原始，并在每次迭代时id增加一个计数器lvl

• 最高的外部查询过滤lvl器id

DB Fiddle 上的演示：

编号 | new_id
--: | -----：
150 | 140
280 | 140
290 | 190
390 | 190
490 | 190
580 | 140
680 | 140
780 | 140
990 | 620