孩子在同一張桌子上的父母 - 必須有更好的方法來做到這一點

我想將孩子與同一張桌子的父母聯繫起來。

表是這樣建構的。身份證，父母，姓名

我想要完整的路徑

$$ name $$沒有像我在範例中那樣指定級別的列。我希望有一種更好的方法來顯示具有無限級別的父級和子級的路徑。我希望這是有道理的 這就是我所擁有的

SELECT  case
           when s6.id is null and s5.id is null and s4.id is null and s3.id is null and s2.id is null  then s1.name
           when s6.id is null and s5.id is null and s4.id is null and s3.id is null                    then s2.name || ' > ' || s1.name
           when s6.id is null and s5.id is null and s4.id is null                                      then s3.name || ' > ' || s2.name || ' > ' || s1.name
           when s6.id is null and s5.id is null                                                        then s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name
           when s6.id is null                                                                          then s5.name || ' > ' || s4.name || ' > ' || s3.name || ' > ' || s2.name || ' > ' || s1.name
           else 'n/a' 
           end as path         
FROM    mytable s1
       LEFT JOIN mytable s2 ON s1.parentid = s2.id
       LEFT JOIN mytable s3 ON s2.parentid = s3.id
       LEFT JOIN mytable s4 ON s3.parentid = s4.id
       LEFT JOIN mytable s5 ON s4.parentid = s5.id
       LEFT JOIN mytable s6 ON s5.parentid = s6.id
;

提前致謝

您正在尋找的是遞歸查詢：

with recursive nodes(id, path) as (
-- selecting the parent/root (anchor)
   select r.id, r.name
   from mytable r
   where r.id = <choose starting root id>
  
union all
-- recursively going through child nodes (recursive member)
   select c.id, concat(path, '->', c.name)
   from mytable c
       join nodes as n 
       on n.id = c.parent_id
)
select *
from nodes
order by path;

請記住，如果您有自引用成員（或在圓圈中相互引用的成員），這將進入無限循環！因此，您必須使用 where 語句排除此類情況，或者不允許任何會導致此類情況的插入/更新。

引用自：https://dba.stackexchange.com/questions/311189