Oracle SQ通过兄弟姐妹识别兄弟姐妹

Question

我已经将一组记录链接在一起，这些记录是通过一个共享的父母来关联的。不幸的是，有一些相当复杂的家庭群体，很明显，仅仅使用共享的父母关系是不够的-我也想考虑到兄弟姐妹关系。

要明确的是，这是实际的家庭群体，它们目前被确认为是共同的父母关系，但在某些情况下，孩子可能不会与另一个仍然通过兄弟姐妹联系在一起的孩子分享父母。

​

所以在上面的例子中，卢和史黛西没有共同的父母关系，但是史黛西是Nate的妹妹，她是Deb的兄弟，Deb是卢的妹妹，把他们联系在一起。

为了便于讨论，假设我们有一些类似于这样的SQL：

SELECT A.ID, A.SIBS FROM A

生成这样的数据集：

ID  SIBS
A   B
A   C
B   A
C   A
C   D
D   C

我想从上面的数据集中生成一个表，该表考虑到兄弟姐妹的兄弟姐妹--例如，兄弟C与兄弟D和兄弟A相关，但通过兄弟A与兄弟B相关。由此产生的表如下所示：

ID  SIBS
A   B
A   C
A   D
B   C
B   D
B   A
C   A
C   D
C   B
D   C
D   A
D   B

如有任何建议，将不胜感激。

Answer 1

不清楚这些关系是否是自反的(也就是说，如果B是A的“兄弟”，那么A是B的“兄弟”)，因为您有一些重复的行与数据中的反向关系，而有些行的属性并不明显。

假设你的关系不是自反的，那么：

SQL Fiddle

Oracle 11g R2架构设置

CREATE TABLE A ( ID, SIBS ) AS
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'A', 'C' FROM DUAL UNION ALL
SELECT 'B', 'A' FROM DUAL UNION ALL
SELECT 'C', 'A' FROM DUAL UNION ALL
SELECT 'C', 'D' FROM DUAL UNION ALL
SELECT 'D', 'C' FROM DUAL UNION ALL
SELECT 'E', 'F' FROM DUAL UNION ALL
SELECT 'F', 'G' FROM DUAL UNION ALL
SELECT 'G', 'H' FROM DUAL;

查询1

SELECT DISTINCT
       CONNECT_BY_ROOT( ID ) AS ID,
       SIBS
FROM   A
WHERE  CONNECT_BY_ROOT( ID ) <> SIBS
CONNECT BY NOCYCLE
       PRIOR SIBS = ID
ORDER BY ID, SIBS

结果

| ID | SIBS |
|----|------|
|  A |    B |
|  A |    C |
|  A |    D |
|  B |    A |
|  B |    C |
|  B |    D |
|  C |    A |
|  C |    B |
|  C |    D |
|  D |    A |
|  D |    B |
|  D |    C |
|  E |    F |
|  E |    G |
|  E |    H |
|  F |    G |
|  F |    H |
|  G |    H |

查询2：如果它们是自反式的，那么您可以使用UNION [ALL]向相反方向的关系复制表，然后使用前面的技术：

SELECT DISTINCT
       CONNECT_BY_ROOT( ID ) AS ID,
       SIBS
FROM   (
  SELECT ID, SIBS FROM A
  UNION
  SELECT SIBS, ID FROM A
)
WHERE  CONNECT_BY_ROOT( ID ) <> SIBS
CONNECT BY NOCYCLE
       PRIOR SIBS = ID
ORDER BY ID, SIBS

结果

| ID | SIBS |
|----|------|
|  A |    B |
|  A |    C |
|  A |    D |
|  B |    A |
|  B |    C |
|  B |    D |
|  C |    A |
|  C |    B |
|  C |    D |
|  D |    A |
|  D |    B |
|  D |    C |
|  E |    F |
|  E |    G |
|  E |    H |
|  F |    E |
|  F |    G |
|  F |    H |
|  G |    E |
|  G |    F |
|  G |    H |
|  H |    E |
|  H |    F |
|  H |    G |

Answer 2

作为分层查询的替代方法，还可以使用递归子查询分解。

with r (pno, sibs) as (
  select a.id, a.sibs
  from a
  union all
  select r.pno, a.sibs
  from r
  join a on a.id = r.sibs
)
cycle pno, sibs set is_cycle to 1 default 0
select distinct pno, sibs
from r
where pno != sibs
order by pno, sibs;

PNO SIBS
--- ----
A   B   
A   C   
A   D   
B   A   
B   C   
B   D   
C   A   
C   B   
C   D   
D   A   
D   B   
D   C   

锚成员从表中获取原始数据。递归成员将找到的每一行连接回主表，保留原始的pno (相当于connect_by_root(id))。

我认为，分层查询的性能可能会更好，但这在一定程度上取决于您的数据，因此您可以尝试这两种方法。