Mysql
在 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<>在這裡擺弄