我想要一个查询,它将在传递绑定变量时从表中输出以下内容。数据库为Oracle 11g R2
WITH X AS (SELECT 'A1'col1, 'B1' col2 FROM dual UNION
SELECT 'A1'col1, 'B2' col2 FROM dual UNION
SELECT 'A2'col1, 'B2' col2 FROM dual UNION
SELECT 'A2'col1, 'B3' col2 FROM dual UNION
SELECT 'A3'col1, 'B3' col2 FROM dual UNION
SELECT 'A3'col1, 'B4' col2 FROM dual UNION
SELECT 'A2'col1, 'B4' col2 FROM dual UNION
SELECT 'A4'col1, 'B1' col2 FROM dual UNION
SELECT 'A5'col1, 'B6' col2 FROM dual UNION
SELECT 'A6'col1, 'B4' col2 FROM dual UNION
SELECT 'A7'col1, 'B8' col2 FROM dual )
对于Ex绑定变量。对于col1 :p1 = A2
输出将包含除'A5 -- B6‘和'A7 -- B8’这两行之外的所有行。换句话说,在绑定变量中传递A2时,我需要包含这两列的所有循环关系。预期输出:
Col1 Col2
----- ----
A1 B1
A1 B2
A2 B2
A2 B3
A2 B4
A3 B3
A3 B4
A4 B1
A6 B4
发布于 2018-06-19 03:30:59
您想要所有连接的行。因此,编写一个递归查询。从A2开始,然后找到相关的行。
with cte(col1, col2) as
(
select col1, col2 from x
where 'A2' in (col1, col2)
union all
select x.col1, x.col2
from x
join cte on x.col1 in (cte.col1, cte.col2)
or x.col2 in (cte.col1, cte.col2)
)
cycle col1, col2 set is_cycle to 1 default 0
select distinct col1, col2
from cte
order by col1, col2;
https://stackoverflow.com/questions/50915602
复制相似问题