我想通过使用oracle connect by by先行获取子级信息和一个上级父级信息吗?
例如,以下查询检索子级信息和父级ID,
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
但我也想获得家长信息
LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL MANAGER_NAME
------------------------- ----------- ---------- ----------------------------
King 100 1 ?
Cambrault 148 100 2 ?
Bates 172 148 3 ?
Bloom 169 148 3 .
Fox 170 148 3 .
我如何处理此问题,当我通过先行连接选择子项后应用左联接时,对象顺序混合。
您可以prior
在选择列表中引用值:
SELECT last_name, employee_id, manager_id, LEVEL, prior last_name as manager_name
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL MANAGER_NAME
------------------------- ----------- ---------- ---------- -------------------------
King 100 1
Kochhar 101 100 2 King
Greenberg 108 101 3 Kochhar
Faviet 109 108 4 Greenberg
...
Cambrault 148 100 2 King
Bates 172 148 3 Cambrault
Bloom 169 148 3 Cambrault
Fox 170 148 3 Cambrault
...
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句