sql - postgresql WITH RECURSIVE查询获取类别和子类别

Question

我有如下表格

使用 WITH RECURSIVE 语句我能够得到表的深度

WITH RECURSIVE category_tree(id, name, depth) AS (
  SELECT id, name, ARRAY[id]
  FROM category
  WHERE parentid IS NULL
UNION ALL
  SELECT category.id, category.name, depth || category.id
  FROM category_tree
  JOIN category ON category.parentid=category_tree.id
  WHERE NOT category.id = ANY(depth)
)
SELECT * FROM category_tree ORDER BY id;

我应该如何更改查询以便获得如下输出(直到树的第 n 级)？

我打算使用上面的结果生成如下站点地图:

我目前正在尝试将 LEFT OUTER JOIN 与 WITH RECURSIVE 语句一起使用，但我无法找到如何使用？有人可以指导吗？

Answer 1

对于您描述的输出格式，我不会进行递归查询。递归查询生成您需要转换的行才能获得您的格式。在数据透视查询中，您需要指定您将拥有哪些列。总而言之，这将成为一个长查询，对于只有 4 或 5 列的输出，以下将更直接:

SELECT    c0.name  AS root_name,
          c1.name  AS down1_name,
          c2.name  AS down2_name,
          c3.name  AS down3_name,
          c4.name  AS down4_name,
          c5.name  AS down5_name
FROM      category c0
LEFT JOIN category c1 ON c1.parentid = c0.id
LEFT JOIN category c2 ON c2.parentid = c1.id
LEFT JOIN category c3 ON c3.parentid = c2.id
LEFT JOIN category c4 ON c4.parentid = c3.id
LEFT JOIN category c5 ON c5.parentid = c4.id
WHERE     c0.parentid IS NULL
ORDER BY  c0.id, c1.id, c2.id, c3.id, c4.id, c5.id