我想为关系员工经理中的一组参数运行递归
每个员工被分配给某个经理,每个经理可以被分配给另一个经理…∞
我的数据库:
id name report_to
1 CEO null
2 frank 1
3 john 2
4 CTO null
5 ana 4
6 candy 5
单参数CEO的简单递归子树:
with recursive employee_sub_tree as
(
select id,
name,
report_to
from category
where name = 'CEO' -- this defines the start of the recursion
union all
select child.id,
child.name,
child.report_to
from employee as child
join employee_sub_tree as parent on parent.id = child.report_to -- the self join to report to manager
)
select *
from employee_sub_tree
结果:
1 CEO null
2 frank 1
3 john 2
条件名称为('CEO','CTO'(的查询结果:
id name report_to
1 CEO null
2 frank 1
3 john 2
4 CTO null
5 ana 4
6 candy 5
在上面我有所有想要的记录,但我丢失了谁是最高级别经理的信息:
我正在寻找sql,它将为我返回结果:
top_level_report_to id name report_to
CEO 1 CEO null
CEO 2 frank 1
CEO 3 john 2
CTO 4 CTO null
CTO 5 ana 4
CTO 6 candy 5
非常重要的是,它必须是1个查询,因为有时我正在寻找几个高层经理的结果
将原始名称作为另一列并在末尾聚合:
with recursive sub_tree as (
select id, name, parent_category, name as orig_name
from category
where name in ( 'X', 'Y' ) -- this defines the start of the recursion
union all
select child.id, child.name, child.parent_category, sub_tree.orig_name
from category child join
sub_tree parent
on parent.id = child.parent_category -- the self join to the CTE builds up the recursion
)
select orig_name, count(*)
from sub_tree
group by orig_name;
我不知道你说的"他们结果的总和"是什么意思,所以这只是一个计数。