Oracle connect_by_root和子查询
在过去的几天里，我一直在为我的任务制定一个解决方案，但我似乎找不到答案。
假设我们有一个名为People的表。每个人都有三个字段：name、boss和position。name是一个主键，position只是一个字符串，boss作为外键指向另一个人的name。它创建了一棵普通的树，比如Person1 -> Person2 -> Person3 -> Person4，在那里Person4将是最高的老板，Person1是它的根。为了简单起见，让我们假设没有人有超过3个老板，而Person4是头老板(Person4是唯一拥有boss等于null的人)。
示例路径：
Person7 -> Person4
Person6 -> Person8 -> Person4
Person2 -> Person8 -> Person4
因此，我的任务是:创建一个查询，该查询只使用树操作(connect、connect_by_root等)，为每个position等于"Worker“或"Manager”的人创建一个按层次顺序显示老板姓名的查询。和子查询
输出表必须由5列组成：
Name | Position | Boss 1 | Boss 2 | Boss 3如果任何boss列为null，那么我应该插入一些空格。
到目前为止，这是我的疑问：
select
case
when l = 1 then name else ' ' end as "Name",
position,
case
when l = 2 then name else ' ' end as "Boss 1",
case
when l = 3 then name else ' ' end as "Boss 2",
case
when l = 4 then name else ' ' end as "Boss 3"
from (
select
connect_by_root position as position,
level as l,
name
from
People
connect by prior
boss = name
start with
position = 'Worker'
or position = 'Manager'
);不过，这棵树的每一层都是新的一行，这是我必须避免的。我知道为什么这个查询会产生这样的结果，但是我不知道如何让它遍历树，而不需要在每一步中创建新的行。
我的结果是：
Name |Position|Boss 1|Boss 2|Boss 3
JOHN WORKER
WORKER HENRY
WORKER PETER
TERRY WORKER
WORKER PETER
ALICE WORKER
WORKER PETER
BILL MANAGER
MANAGER JAMES
MANAGER PETER 这是我想要达到的结果：
Name |Position|Boss 1|Boss 2|Boss 3
JOHN WORKER HENRY PETER
TERRY WORKER PETER
ALICE WORKER PETER
BILL MANAGER JAMES PETER 在没有的情况下，是否有任何解决方案()使用诸如Pivot这样的复杂函数来使其工作？
回答 2
Stack Overflow用户
发布于 2016-11-04 15:50:57
它应该是这样工作的(我没有需要检查的源数据)：
select
root_name as "Name",
max(position),
max(case when l = 2 then name else null end) as "Boss 1",
max(case when l = 3 then name else null end) as "Boss 2",
max(case when l = 4 then name else null end) as "Boss 3"
from (
select
connect_by_root position as position,
connect_by_root name as root_name,
level as l,
name
from
People
connect by prior
boss = name
start with
position = 'Worker'
or position = 'Manager'
)
group by root_name;Stack Overflow用户
发布于 2016-11-04 15:24:44
完全可以通过使用CONNECT_BY_ROOT、CONNECT_BY_ISLEAF和SYS_CONNECT_BY_PATH，加上明智地使用REGEXP_SUBSTR来实现这一点，而不需要任何枢轴等。
WITH people AS (SELECT 'JOHN' name, 'WORKER' position, 'HENRY' boss FROM dual UNION ALL
SELECT 'HENRY' name, 'CFO' position, 'PETER' boss FROM dual UNION ALL
SELECT 'TERRY' name, 'WORKER' position, 'PETER' boss FROM dual UNION ALL
SELECT 'ALICE' name, 'WORKER' position, 'PETER' boss FROM dual UNION ALL
SELECT 'JAMES' name, 'CIO' position, 'PETER' boss FROM dual UNION ALL
SELECT 'FRED' name, 'MANAGER' position, NULL boss FROM dual UNION ALL
SELECT 'BILL' name, 'MANAGER' position, 'JAMES' boss FROM dual UNION ALL
SELECT 'PETER' name, 'CEO' position, 'FRED' boss FROM dual)
-- end of mimicking your people table with some sample data in it
-- you wouldn't need the above, just use the query below:
SELECT connect_by_root name AS name,
connect_by_root position AS position,
regexp_substr(sys_connect_by_path(boss, '>'), '[^>]+', 1, 1) boss1,
regexp_substr(sys_connect_by_path(boss, '>'), '[^>]+', 1, 2) boss2,
regexp_substr(sys_connect_by_path(boss, '>'), '[^>]+', 1, 3) boss3
FROM people
WHERE connect_by_isleaf = 1
CONNECT BY PRIOR boss = name
START WITH position IN ('WORKER', 'MANAGER');
NAME POSITION BOSS1 BOSS2 BOSS3
----- -------- ----- ----- -----
ALICE WORKER PETER FRED
BILL MANAGER JAMES PETER FRED
FRED MANAGER
JOHN WORKER HENRY PETER FRED
TERRY WORKER PETER FRED CONNECT_BY_ISLEAF确定该行是否为叶行(1) (0)。因此，就像您可以使用CONNECT_BY_ROOT识别根值一样，您也可以使用CONNECT_BY_ISLEAF判断哪一行是结束行。
SYS_CONNECT_BY_PATH生成一个路径，其中包含到目前为止所要达到的所有值。因此，在叶行上，它将包含所有必需的值。然后，我们可以解析这个生成的字符串，以获得不包含路径分隔符的第一个、第二个等等部分。
https://stackoverflow.com/questions/40425105
复制相似问题
腾讯云开发者
