Oracle SQ通过同级标识同级 [英] Oracle SQ Identify Siblings via siblings

问题描述

我已将通过共享父项关联的记录组链接在一起.不幸的是，有一些相当复杂的家庭团体，而且很明显，仅仅使用共享的父母关系是不够的-我也想考虑同胞关系.

I have linked together groups of records which are related via a shared parent. Unfortunately there are some quite complicated family groups, and it's become clear that just using a shared parental relationship isn't enough - I also want to take into account sibling relationships.

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

To be clear - this is actual family groups, which are currently identified if their is a shared mother or father relationship, but in some cases a child may not share parents with another child who is still linked to them via a sibling.

因此，在以上示例中，Lou与Stacey没有共同的父母关系，但是Stacey是Nate的姐姐，而Nate是Deb的弟弟，而Deb是Lou的姐姐，后者将他们联系在一起.

So in the above example, Lou has no shared parental relationship with Stacey, but Stacey is the sister of Nate who is the brother of Deb who is the sister of Lou, which links them together.

为了论证，我们假设我们有一些这样的SQL:

For arguments sake let's say we have some SQL like this:

SELECT A.ID, A.SIBS FROM A

哪个会生成这样的数据集:

Which produces a dataset like this:

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

我想从上述数据集中生成一个表，该表考虑了同级的同级-例如同级C与同级D和同级A相关，但与同级B通过同级A相关.结果表看起来像这样:

I would like to produce a table from the above dataset which takes into account siblings of siblings - for example sibling C is related to sibling D and Sibling A, but is related to sibling B via sibling A. The resulting table would look like this:

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

任何建议将不胜感激.

推荐答案

目前尚不清楚这些关系是否是自反的(即，如果 B 是 A 的同级"，则 A 是 B 的同级")，因为您的数据中有一些重复的行具有相反的关系，而有些行的属性不明显.

Its unclear whether the relationships are reflexive (i.e. if B is a "sibling" of A then A is a "sibling" of B) as you have some duplicate rows with the reversed relationships in your data and some where this property is not evident.

假设您的关系不是自反的，那么:

Assuming that your relationships are not reflexive then:

SQL小提琴

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

结果 :

Results:

| 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] 复制具有反向关系的表，然后使用以前的技术:

Query 2: If they are reflexive then you can use UNION [ALL] to duplicate the table with the relationships in the reverse direction and then use the previous technique:

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

结果 :

Results:

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

这篇关于Oracle SQ通过同级标识同级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！