sql - 从父元素获取属性值 - 仅通过 sql

Question

• 我和员工有一张 table 。
• 每个员工都可以有一个老板/ parent (但也可以为空)。
• 每个高层员工在 floor 列中都有一个值。
• 他所有的 child 都继承了这个代码中的值，但不在数据库中。

示例:

| id    | name    | parent_id | floor    |
|----------------------------------------|
| 1     | boss1   | null      | green    | 
| 2     | emp1    | 1         | null     |
| 3     | boss2   | null      | blue     |
| 4     | emp3    | 3         | null     |
| 5     | emp4    | 2         | null     |

现在我想回答例如以下问题，哪些员工在绿色楼层工作(显而易见的答案:boss1、emp1 和 emp4)？我知道这在编程上很容易，但我只想使用 SQL (Postgres) 来完成。

提前致谢!

Answer 1

你需要一个 recursive common table expression穿过树。您还需要将非空值从父级“携带”到子级以模拟值的继承。

with recursive tree as (
   select id, name, parent_id, floor
   from employees
   where parent_id is null
   union all
   select c.id, c.name, c.parent_id, coalesce(c.floor, p.floor) as floor
   from employees c 
     join tree p on p.id = c.parent_id
)
select *
from tree;

根据您的示例数据，上述返回:

id | name  | parent_id | floor
---+-------+-----------+------
 1 | boss1 |           | green
 3 | boss2 |           | blue 
 2 | emp1  |         1 | green
 4 | emp3  |         3 | blue 
 5 | emp4  |         2 | green

现在可以通过向最终选择添加 WHERE 条件来更改以返回所有带有绿色地板的行。

with recursive tree as (
   ... as above ...
)
select *
from tree
where floor = 'green';

在线示例:https://rextester.com/UQJVF54349