如何为第一个条目仅显示"老板"？

Question

嗨，

我正试图从给定的表中找到每个员工的上级/经理，路径应以"Boss->"开头，在每个经理的last_name之后都应出现"->'"，并且路径应以该员工的经理的lastname结束。我使用的是递归CTE，但不知何故，我从查询中获得的数据是错误的。我有以下结果表：

id | first_name | last_name | superior_id |               path                |
----+------------+-----------+-------------+-----------------------------------+
1 | Simon      | Dixon     | null        | Boss->Dixon                       |
2 | Alfredo    | Garza     | 1           | Boss->Garza->Garza                |
3 | Martin     | Lopez     | 1           | Boss->Lopez->Lopez                |
4 | Jorge      | Fox       | 1           | Boss->F->Fox                      |
5 | Isaac      | Campbell  | 2           | Boss->Garza->Campbell->Campbell   |
6 | Rosemary   | Mcguire   | 3           | Boss->Lopez->Mcguire->Mcguire     |
7 | Jake       | Griffin   | 3           | Boss->Lopez->Griff->Griffin       |
10 | Thelma     | Lindsey   | 4           | Boss->F->Lindsey->Lindsey         |
8 | Garrett    | Grant     | 7           | Boss->Lopez->Griff->Grant->Grant  |
9 | Deanna     | Olson     | 5           | Boss->Garza->Campbell->Ols->Olson |

并且我得到的查询结果是上表：

WITH RECURSIVE hiearchy AS (
SELECT
id,
first_name,
last_name,
superior_id,
'Boss' AS path
FROM employee
WHERE superior_id IS NULL
UNION
SELECT
employee.id,
employee.first_name,
employee.last_name,
employee.superior_id,
concat(trim(path,'->Dixon'), '->', employee.last_name)
FROM employee join hiearchy
On employee.superior_id=hiearchy.id
)
SELECT
id,
first_name,
last_name,
superior_id,
concat(trim(path,'->Dixon'), '->', last_name) as path
From hiearchy;

有人能指导我找到解决方案吗？谢谢

Answer 1

我认为您需要为树中的每个元素生成分层路径：

with recursive hiearchy as (
select
id,
first_name,
last_name,
superior_id,
'boss' as path
from employee
where superior_id is null
union all
select
e.id,
e.first_name,
e.last_name,
e.superior_id,
path || '->' || h.last_name
from employee e
join hiearchy h on e.superior_id = h.id
)
select h.* from hiearchy h;

对于您的样本数据，这会生成一个结果集，如：

id|first_name|last_name|superior_id|path-：|：---------|：---------|---------:|：---------------------------1|Simon |Dixon|null|老板2|Alfredo|Garza|1|老板->Dixon3|马丁|洛佩兹|1|老板->Dixon4|Jorge|Fox|1|老板->Dixon5|Isaac|Campbell|2|老板->Dixon->加尔扎6|罗斯玛丽|Mcguire |3|老板->Dixon->洛佩兹7|杰克|Griffin|3|老板->Dixon->洛佩兹10|Thelma|Lindsey |4|老板->Dixon->狐8|Garrett|Grant|7|老板->Dixon->洛佩兹->格里芬9|Deanna|Olson|5|老板->Dixon->加尔扎->坎贝尔