Oracle 10g PL/SQL Connect By Prior在同一行上返回子级和父级 [英] Oracle 10g PL/SQL Connect By Prior returning Child and Parent on same row
问题描述
Table1:
Child Parent a
Bob Chris 2
Chris Kate 1
Shane Lana 3
Nala Bob 4
Table2:
b Talent
1 'something'
2 'nothing'
3 'something'
4 'nothing'
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
此代码返回父母的行
克里斯
如果不包括"Where"子句,则代码将返回:
If the "Where" clause was not included, the code would return:
Bob
Chris
Kate
Chris
Kate
Shane
Lana
etc
我要返回的是连续的内容,而不是列中的内容:
What I want to return is the following in a row, not in columns:
鲍勃·克里斯
克里斯(Chris)是有才华的人,并且是鲍勃(Bob)的父母,因此代码不仅返回父母,而且还返回为该父母发起查询的孩子,因此在此代码中:
Where Chris is the one with a talent and is parent to Bob, so the code returns not only the parent, but also the child which originated the query for that parent, so in this code:
SELECT Child
FROM Table1
INNER JOIN Table2 ON (Table1.a = Table2.b)
WHERE Table2.Talent = 'something'
connect by prior Table1.Child = Table1.Parent
我将拥有具有才能的Child chris和最初寻找chris的前一个Bob,因此可以说,如果Bob是Gala的孩子,而Gala是Chris的孩子,我仍然只想得到Bob和Chris结果.
I would have the Child chris with the talent and the previous child Bob who originated the search for chris, so lets say if Bob was child to Gala and Gala was child to Chris, I would still want to get only Bob and Chris in the result.
条件:我无权创建临时表或任何类型的表,因此除非使用临时表,否则我不能使用任何循环来执行此操作.
Conditions: I do not have the permission to create temporary or any sort of tables, so I cannot use any loop to do this unless I just do not know how to do it without a temporary table
我不知道如何才能从"prior"语句和新的"child"(实际上是前一个孩子的父母)之前返回一个孩子.
I do not know how I can return a child from before the "prior" statement and the new "child" which is actually the parent of the previous child.
推荐答案
您可以使用"HR"数据库进行测试
You can use "HR" database to Test
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path"
FROM employees
WHERE LEVEL > 1 and department_id = 110
CONNECT BY PRIOR employee_id = manager_id;
Employee Manager Pathlen Path
--------------- ------------ ---------- ----------------------------
Higgins Kochhar 1 /Kochhar/Higgins
Gietz Kochhar 2 /Kochhar/Higgins/Gietz
Gietz Higgins 1 /Higgins/Gietz
Higgins King 2 /King/Kochhar/Higgins
Gietz King 3 /King/Kochhar/Higgins/Gietz
这篇关于Oracle 10g PL/SQL Connect By Prior在同一行上返回子级和父级的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!