sql - PostgreSQL 合并递归查询和 JOIN

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作为员工 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 here
提前致谢

Answer 1

我觉得你已经为这个问题做了大部分工作。这只是以下调整的问题:

将第一个查询的逻辑放在 CTE 的 anchor 部分。

将原始服务 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;

Here是一个db<> fiddle 。