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屋！