MySQL:查找顶级/终极父级

Question

我找到了一些类似的东西，但都不是我所需要的。

ID  |   PARENT
1   |   NULL
2   |   5
3   |   6
4   |   6
5   |   NULL
6   |   9
7   |   NULL
8   |   7
9   |   8
10  |   NULL

我有一个类似于下表的表格，需要使用MySQL查找最顶层的父母。我在这方面遇到的麻烦是，在达到顶层父母的过程中，可能需要多达20个步骤，而我认为我对MySQL还不够精通，无法弄清楚这一点。

提前感谢！

Answer 1

这是我的回答。这不是很好，但它工作。第一个查询是使用序列引擎的MariaDB，较长的查询来自"normal“MySQL

启动参数(id)必须在此处设置@sid := 8

MariaDB

SELECT path, sid
FROM (
  SELECT *, @sid AS sid, @path := CONCAT(@path,' -> ', @sid) AS path,
  (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent  
  FROM seq_1_to_99
  CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
  HAVING parent = @sid
) AS result
ORDER BY seq DESC
LIMIT 1;

MySQL

SELECT path, sid FROM (
SELECT *, @sid AS sid,
 @path := CONCAT(@path,' -> ', @sid) AS PATH,
 (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent

FROM (
  SELECT *
  FROM (
    SELECT d2.a*10+d1.a  AS nr
    FROM (
        SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
        SELECT 8   UNION ALL SELECT 9) AS d1
      CROSS JOIN (
        SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
  ) AS counter
  ORDER BY counter.nr
) AS result
CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
HAVING parent = @sid
) AS p
ORDER BY nr DESC
LIMIT 1;

示例

MariaDB []> select * from mytab;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |      3 |
|  3 |   NULL |
|  4 |   NULL |
|  5 |      4 |
|  6 |      5 |
|  7 |      8 |
|  8 |      6 |
+----+--------+
8 rows in set (0.00 sec)

MariaDB []> SELECT path, sid
    -> FROM (
    ->   SELECT *, @sid AS sid, @path := CONCAT(@path,' -> ', @sid) AS path,
    ->   (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent
    ->   FROM seq_1_to_99
    ->   CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
    ->   HAVING parent = @sid
    -> ) AS result
    -> ORDER BY seq DESC
    -> LIMIT 1;
+-------------+------+
| path        | sid  |
+-------------+------+
| 8 -> 6 -> 4 | 4    |
+-------------+------+
1 row in set (0.01 sec)

MariaDB []> SELECT path, sid FROM (
    -> SELECT *, @sid AS sid,
    ->  @path := CONCAT(@path,' -> ', @sid) AS PATH,
    ->  (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent
    ->
    -> FROM (
    ->   SELECT *
    ->   FROM (
    ->     SELECT d2.a*10+d1.a  AS nr
    ->     FROM (
    ->         SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    ->         SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    ->         SELECT 8   UNION ALL SELECT 9) AS d1
    ->       CROSS JOIN (
    ->         SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
    ->   ) AS counter
    ->   ORDER BY counter.nr
    -> ) AS result
    -> CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
    -> HAVING parent = @sid
    -> ) AS p
    -> ORDER BY nr DESC
    -> LIMIT 1;
+-------------+------+
| path        | sid  |
+-------------+------+
| 8 -> 6 -> 4 |    4 |
+-------------+------+
1 row in set (0.01 sec)

MariaDB []>

我希望这对你有一点帮助。