sql - 如何根据树中的任何节点获取自引用的表树结构？

Question

我正在寻找一种方法，基于任何节点 ID 查询整个树结构（包括顶部父级，到底部子级） - 无论如何，通过基于顶部父级、内部任何节点或非常底部的子级进行查询。

即有表：

|--------------|
|ID  | ParentID|
|--------------|
|229 | NULL    |
|230 | 229     |
|231 | 229     |
|232 | 229     |
|233 | 229     |
|              |
|300 | NULL    |
|301 | 300     |
|302 | 301     |
|303 | 302     |
|304 | 300     |
----------------

基于查询229、或230、或231、或232或233，结果应始终相同：

229
230
231
232
233

基于查询300，或301，或302，或303，或304，结果应始终相同：

300
301
302
303
304

标签： sqlsql-server

Answer 1

您可以使用递归 CTE 将每个 id 分配给其最终父级。然后，您可以使用此信息从父级获取所有 id：

with t as (
      select v.*
      from (values (229, null), (230, 229), (231, 229), (232, 229), (233, 229), 
                   (300, NULL), (301, 300), (302, 301), (303, 300 )
           ) v(id, parentid)
     ),
     cte as (
      select id as ultimate_parent_id, id as relatedid
      from t
      where parentid is null
      union all
      select cte.ultimate_parent_id, t.id
      from cte join
           t
           on cte.relatedid = t.parentid
     )
select relatedid
from cte
where ultimate_parent_id = (select ultimate_parent_id
                            from cte
                            where relatedid = 231
                           );

这是一个 db<>fiddle。