PostgreSQL递归合并查询和联接

Question

我有以下模式：

CREATE TABLE tbl_employee_team
(
  employee_id int,
  teams_id int
);

INSERT INTO tbl_employee_team 
VALUES 
(1, 2),
(1, 3),
(1, 4);

CREATE TABLE tbl_team_list_serv
(
  service_id int,
  team_id int
);

INSERT INTO tbl_team_list_serv
VALUES
(7, 2),
(9, 3),
(10, 4);

CREATE TABLE tbl_service
(
  id int,
  parent int
);

INSERT INTO tbl_service
VALUES
(5, null),
(6, 5),
(7, 6),

(8, null),
(9, 8),

(10, null);

为了简化起见，我声明:

1 为 employee_id

2, 3, 4 为 team_id

5 -> 6 -> 7 为服务（5 是主要服务）

8 -> 9 （8 是主要服务）

10 （10 是主要服务）

要检索员工所属的服务，我查询：

SELECT ls.service_id FROM tbl_team_list_serv ls 
JOIN tbl_employee_team t ON ls.team_id=t.teams_id WHERE t.employee_id = 1

获取我使用的服务中的主要服务

WITH RECURSIVE r AS 
(
   SELECT id, parent, 1 AS level
   FROM tbl_service
   WHERE id = 7 /*(here's I need to assign to every id from the JOIN)*/
  
   UNION

   SELECT tbl_service.id, tbl_service.parent, r.level + 1 AS level
   FROM tbl_service
      JOIN r
          ON r.parent = tbl_service.id
)


SELECT id FROM r WHERE r.level = (SELECT max(level) FROM r)

我的问题是如何合并这两个查询？

基于上述数据，我想最终得到的ID列表如下：

5, 8, 10

此外，我希望我的递归查询可以返回最后一行（我认为使用 level 的解决方案不太优雅）。

SQLFiddle 可在 这里 找到。

提前感谢！

- Joe D

---

/（这里我需要为JOIN中的每个id分配）/问题：您需要哪些id？ - Ahmmed

从第一个JOIN中检索到的ID为7、9、10，在这种情况下。 - Joe D

Answer 1

我感觉你已经完成了这个问题的大部分工作。这只是一些小改动：

• 将第一个查询的逻辑放在CTE的锚点部分。
• 添加原始服务ID作为列，以记住层次结构。
• 调整最终逻辑以获得每个原始服务的一行。

作为查询语句：

WITH RECURSIVE r AS  (
     SELECT ls.service_id as id, s.parent, 1 as level, ls.service_id as orig_service_id
     FROM tbl_team_list_serv ls JOIN
          tbl_employee_team t
          ON ls.team_id = t.teams_id JOIN
          tbl_service s 
          ON ls.service_id = s.id
     WHERE t.employee_id = 1
     UNION ALL
     SELECT s.id, s.parent, r.level + 1 AS level, r.orig_service_id
     FROM tbl_service s JOIN
          r
          ON r.parent = s.id
   )
SELECT r.id
FROM (SELECT r.*,
             MAX(level) OVER (PARTITION BY orig_service_id) as max_level
      FROM r
     ) r
WHERE r.level = max_level;

这里有一个数据库测试平台。

- Gordon Linoff

---

非常感谢，您的查询起到了作用。 - Joe D