Oracle11g-使用m到n关系表连接多行的数据

Question

我可能有一个简单的问题，但我仍然不知道如何使事情运作。

我有三个表，其中有以下几个重要的字段：

1. Rule - RuleOID
2. RuleToModificationReason - RuleOID，MdfnReasonOID
3. ModificationReason - MdfnReasonOID，MdfnTyp (创建或删除)，MdfnDetail

还有f.e。我有这样的数据：

1. Rule - RuleOID = 1
2. RuleToModificationReason
   • RuleOID = 1，MdfnReasonOID = 1
   • RuleOID = 1，MdfnReasonOID = 2

​

1. ModificationReason
   • MdfnReasonOID = 1，MdfnTyp = 'CREATION'，MdfnDetail = 'Creation detail'
   • MdfnReasonOID = 2，MdfnTyp =‘MdfnDetail’，MdfnDetail= 'Deletion detail'

‘

​

我希望在一行上有输出，以便用规则样条创建和删除。

f.e.：：RuleOID，创建细节，删除细节。

我可以将它加载到两行如下：

NULL

• RuleOID，

• RuleOID，创建详细信息， NULL，删除细节。

创建和删除原因可以为空。

有可能吗？

Answer 1

如果您确信始终最多有一个创建--给定规则的详细信息，而给定规则最多只有一个删除详细信息--那么您可以使用子查询：

SELECT Rule.RuleOID,
       ( SELECT MdfnDetail
           FROM ModificationReason
          WHERE MdfnType = 'CREATION'
            AND MdfnReasonOID IN
                 ( SELECT MdfnReasonOID
                     FROM RuleToModificationReason
                    WHERE RuleOID = Rule.RuleOID
                 )
       ) AS CreationReason,
       ( SELECT MdfnDetail
           FROM ModificationReason
          WHERE MdfnType = 'DELETION'
            AND MdfnReasonOID IN
                 ( SELECT MdfnReasonOID
                     FROM RuleToModificationReason
                    WHERE RuleOID = Rule.RuleOID
                 )
       ) AS DeletionReason
  FROM Rule
;

(仅用JOIN也可以实现这一点，但这要困难得多。)

Answer 2

这对你有用吗？

SELECT a.RuleOID, 
CASE
  WHEN b.MdfnTyp = 'CREATION' THEN b.MdfnDetail
  ELSE NULL -- can omit, case has default "else null"
END AS creation_detail,
CASE
  WHEN b.MdfnTyp = 'DELETION' THEN b.MdfnDetail
  ELSE NULL -- can omit, case has default "else null"
END AS deletion_detail

RuleToModificationReason a
INNER JOIN ModificationReason b ON (a.MdfnReasonOID = b.MdfnReasonOID)

注意:按目的跳过的规则表(假设引用完整性由约束强制执行)，因为您不使用该表中的任何字段

Answer 3

select crtr.RuleOID RuleOID, crt.MdfnDetail Creation_detail, dlt.MdfnDetail Deletion_detail
from ModificationReason crt, ModificationReason dlt, RuleToModificationReason crtr, RuleToModificationReason dltr
where crt.MdfnTyp = 'CREATION' and dlt.MdfnTyp = 'DELETION'
and crt.MdfnReasonOID = crtr.MdfnReasonOID
and dlt.MdfnReasonOID = dltr.MdfnReasonOID
and crtr.RuleOID = dltr.RuleOID   

(无法检查)