主页 / dba / 问题 / 291321
Accepted
Googlebot
Asked: 2021-05-11 08:20:08 +0800 CST

在 MySQL 中递归获取最高的父 id

  • 772

我想为每个条目找到最上面的父 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 而不是递归。

样品小提琴。

mysql join

2 个回答

  1. Best Answer
    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

    • 4

  2. 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<>在这里摆弄

    • 2

相关问题