sql - 查询邻接表中最远的 child

Question

所以我有一个 SQL 查询来检索邻接列表中给定节点的所有子节点。

WITH    RECURSIVE
        q AS
        (
        SELECT  id, name
        FROM    categories h
        WHERE   id = 11846801
        UNION ALL
        SELECT  hc.id, hc.name
        FROM    q
        JOIN    categories hc
        ON      hc.parent = q.id
        )
SELECT  name
FROM    q

有没有办法修改此查询以仅返回节点的底层？我不能只指定给定的级别，因为每条路径可能具有不同的深度。

Answer 1

解释1

“所有离开节点的路径从一开始就是最长的。”

一种方法是在下降的过程中计算级别，并且只返回底部级别的成员:

WITH RECURSIVE q AS (
   SELECT  id, name, 0 AS lvl
   FROM    categories
   WHERE   id = 11846801

   UNION ALL
   SELECT  c.id, c.name, q.lvl + 1
   FROM    q
   JOIN    categories c ON c.parent = q.id
   )
SELECT  id, name
FROM    q
WHERE   lvl = (SELECT max(lvl) FROM q);

解释2

“所有离开节点。”

WITH RECURSIVE q AS (
   SELECT  id, name, parent
   FROM    categories
   WHERE   id = 11846801

   UNION ALL
   SELECT  c.id, c.name, c.parent
   FROM    q
   JOIN    categories c ON c.parent = q.id
   )
SELECT  id, name
FROM    q
WHERE   NOT EXISTS (SELECT FROM q q1 WHERE q1.parent = q.id);

检查 q 应该比检查基表更快 - 除非 q 非常大，在这种情况下，主表上的索引可能更快。