在 MySQL 中遞歸獲取最高的父 id

我想為每個條目找到最上面的父 ID。例如，

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

我打算得到

ID     Highest ParentID
1      NULL
2      1
3      1
4      1

我計劃創建一個遞歸查詢（MySQL 8 或 MariaDB 10.5），方法是在父 ID 為特定值（例如，在上述情況下為 NULL）時添加一個條件來中斷遞歸。我從

WITH RECURSIVE cte (ID, ParentID) as (
 SELECT ID,ParentID FROM t1
 UNION ALL
 SELECT t2.ID,t2.ParentID FROM t1 t2
 INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

但它不像我想要的那樣工作，因為它獲取下一個 ParentID 而不是遞歸。

樣品小提琴。

WITH RECURSIVE 
cte as ( SELECT id, id nextid, parentid 
        FROM t1
      UNION ALL
        SELECT cte.id, t1.id, t1.parentid
        FROM t1
        JOIN cte ON cte.parentid = t1.id )
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=209df940143d3e984d418e49929bd847

Akinas cte 的一種可能性是

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3),(5,NULL),(6,5),(7,6);

SELECT * FROM t1;

✓

✓

身份證 | 父母ID
-: | -------:
 1 | *空值*
 2 | 1
 3 | 2
 4 | 3
 5 | *空值*
 6 | 5
 7 | 6

WITH RECURSIVE cte (ID, ParentID, path) as (
  SELECT ID,ParentID, ID path
  FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID, cte.path 
  FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT ID,MIN(PATH) FROM cte GROUP BY ID;

身份證 | 最小（路徑）
-: | --------:
 1 | 1
 2 | 1
 3 | 1
 4 | 1
 5 | 5
 6 | 5
 7 | 5

db<>在這裡擺弄

對第一個的改編，如果數字不是sequentiell，你需要從

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,10),(10,20),(20,NULL),(2,1),(3,2),(4,3),(5,NULL),(6,5),(7,6);

SELECT * FROM t1;

✓

✓

身份證 | 父母ID
-: | -------:
 1 | 10
 2 | 1
 3 | 2
 4 | 3
 5 | *空值*
 6 | 5
 7 | 6
10 | 20
20 | *空值*

WITH RECURSIVE cte (ID, ParentID, path) as (
  SELECT ID,ParentID, ID path
  FROM t1
  WHERE ParentID IS NULL

  UNION ALL
  SELECT t2.ID,t2.ParentID, cte.path 
  FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT ID,MIN(PATH) FROM cte GROUP BY ID;

身份證 | 最小（路徑）
-: | --------:
 5 | 5
20 | 20
 6 | 5
10 | 20
 1 | 20
 7 | 5
 2 | 20
 3 | 20
 4 | 20

db<>在這裡擺弄

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