sql - 在层次结构表 PostgreSQL 上选择查询

Question

我试图在 postgreSQL 中进行递归选择查询，我试图阅读几篇文章，但我无法真正从中找到解决方案......要么是多表层次结构，要么是另一种方言我无法翻译成 Postgres。

这是我现在想到的，但它似乎只检索一列，所以有些东西不太管用

WITH RECURSIVE authorities(role_id, role_name, fk_above_role) AS (
SELECT role_id,role_name,fk_above_role
FROM roles
WHERE role_id=1
UNION ALL
SELECT h.id_role, h.role_name, h.fk_above_role
FROM roles h
WHERE h.role_id= h.fk_above_role
)
SELECT * FROM authorities LIMIT 2;

为了尝试，我将限制设置为 2。所以基本上假设我有一张像这样制作的 table

role_id   |    role_name    |   fk_above_role
1              HRManager        null
2              Recruiter        1

在这种情况下，我进行的递归查询是检索所有 HRManager 子角色，我该怎么做？我最终还需要获得招聘人员子角色，这些子角色也被认为是 HRManager 子角色。我做错了什么？

Answer 1

需要在UNION的递归部分加入递归CTE:

WITH RECURSIVE authorities(role_id, role_name, fk_above_role) AS (
  SELECT role_id,role_name,fk_above_role
  FROM roles
  WHERE role_id = 1
  UNION ALL
  SELECT child.id_role, child.role_name, child.fk_above_role
  FROM roles child
     JOIN authorities parent on child.fk_above_rold = parent.role_id
)
SELECT * 
FROM authorities LIMIT 2;