我有以下模式:
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 可在 这里 找到。
提前感谢!
7、9、10
,在这种情况下。 - Joe D