Oracle SQL将关系嵌套到一个级别 [英] Oracle SQL nested relationship into one level

问题描述

ColumnA ColumnB  ColumnC ColumnD
A        B        C        E
D        C        F        E
C        H        I        E
C        W        S        E1

逻辑应该是，当columnA/columnB在columnC中有一条记录，而该记录在另一列具有相同columnD的记录中是columnA/columnB时，将输出以下内容

The logic should be when columnA/columnB has a record in columnC which is columnA/columnB in another record with the same columnD, it will be output as the following

ColumnV  ColumnW
A          C
B          C
D          F
C          F
C          I
H          I
C          S
W          S
A          F
B          F
A          I
B          I 

如何编写sql以获取以下内容:

How can I write a sql to get the following:

推荐答案

我不确定我是否完全了解您要实现的逻辑，但是这里的SQL创建了您的表并复制了示例输出.已在 https://livesql.oracle.com

I'm not sure if I fully understand the logic you are trying to implement, but here is SQL that creates your table and duplicates your example output. It was tested on https://livesql.oracle.com

请稍加注意，因为如果您的数据可能具有重复的行或循环或其他内容(在您的示例中未显示)，则查询可能需要修改.

Please take this with a grain of salt because if your data may have duplicate rows or cycles or whatnot, that is not demonstrated in your example, the query might need modification.

概述:

1. 在"with"子句中，将"ColumnA"和"ColumnB"旋转到单个列中，并添加col_src以保留新的"ColumnAB"是哪一个.

1. In the "with" clause, we pivot "ColumnA" and "ColumnB" into a single column, and add col_src to preserve which one the new "ColumnAB" is.

然后我们递归查询，通过匹配的列D和与上一个列C匹配的列A/B进行连接.

Then we recursively query, connecting by a matching column D and a column A/B that matches the previous column C.

为匹配提供的顺序，我们按以下顺序排序:

To match the ordering provided, we sort by:

• 递归级别
• 列C
• 来源是A列还是B列
• A列或B列的值

create table mytable as
select 'A' "ColumnA",'B' "ColumnB",'C' "ColumnC",'E' "ColumnD" from dual
union select 'D' "ColumnA",'C' "ColumnB",'F' "ColumnC",'E' "ColumnD" from dual
union select 'C' "ColumnA",'H' "ColumnB",'I' "ColumnC",'E' "ColumnD" from dual
union select 'C' "ColumnA",'W' "ColumnB",'S' "ColumnC",'E1' "ColumnD" from dual
;

with temp as (
    select "ColumnA" as "ColumnAB", "ColumnC", "ColumnD", 'A' as col_src
    from mytable
    union all select "ColumnB", "ColumnC", "ColumnD", 'B' as col_src
    from mytable
)
select connect_by_root("ColumnAB") "ColumnV", "ColumnC" as "ColumnW" from temp
connect by prior "ColumnD" = "ColumnD" and prior "ColumnC" = "ColumnAB"
order by level,"ColumnC",col_src,  "ColumnAB"

这篇关于Oracle SQL将关系嵌套到一个级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！