选择在另一列中不匹配的所有项目

Question

我有一张桌子

type     item     yield    section
-----------------------------------
CT1   |    A   |   25   |   M-2
CT1   |    A   |   35   |   M-1
CT2   |    A   |   70   |   M-1
CT2   |    A   |   30   |   M-2
CT2   |    A   |   20   |   M-3
CT1   |    B   |   40   |   M-2
CT1   |    B   |   15   |   M-1
CT2   |    B   |   25   |   M-1
CT2   |    B   |   25   |   M-2

首先，我需要用Item and Type对所有的yield求和，然后除以我的表中每行的每个收益率。其次，我需要知道每个costtypcd, item and section的yield是相同的(即，type CT1 item A section M-1的收益率为.58 (25+35=60，则35/60为.58)和type CT2 item。section M-1的收益率也为.58，因此我不想选择该项目，因为它是匹配的)。

到目前为止，我的代码只能得到收益率(已经计算过了，但不知道如何只选择那些在yield,item and section中没有匹配的items

select type,item,yield, section
FROM(
Select type, item,
       Round(yield/Sum(yield) Over (Partition By type,item),2) As yield,section
From MyTable
Order By item
)

因此，我的问题是，如何才能在具有相同item,section and different type的yield中获取仅具有匹配项的所有项目

在我的表中，只有CT1 A M-1和CT2 A M-1在产量上是匹配的，所以我想选择所有剩余的项。

预期输出：

type     item     section
--------------------------
CT1   |    A   |    M-2
CT2   |    A   |    M-2
CT2   |    A   |    M-3
CT1   |    B   |    M-2
CT1   |    B   |    M-1
CT2   |    B   |    M-1
CT2   |    B   |    M-2    

我不需要展示产量。

Answer 1

with
     inputs ( type, item, yield, section ) as (
       select 'CT1', 'A', 25, 'M-2' from dual union all
       select 'CT1', 'A', 35, 'M-1' from dual union all
       select 'CT2', 'A', 70, 'M-1' from dual union all
       select 'CT2', 'A', 30, 'M-2' from dual union all
       select 'CT2', 'A', 20, 'M-3' from dual union all
       select 'CT1', 'B', 40, 'M-2' from dual union all
       select 'CT1', 'B', 15, 'M-1' from dual union all
       select 'CT2', 'B', 25, 'M-1' from dual union all
       select 'CT2', 'B', 25, 'M-2' from dual
     ),
     prep ( type, item, yield, section, pct ) as (
       select type, item, yield, section,
              yield / sum(yield) over (partition by type, item)
       from   inputs
     ),
     final ( type, item, yield, section, pct, ct ) as (
       select type, item, yield, section, pct, 
              count(distinct type) over (partition by item, section, pct)
       from   prep
     )
select type, item, section
from   final
where  ct = 1
;

输出：

TYPE ITEM SECTION
---- ---- -------
CT2  A    M-2
CT1  A    M-2
CT2  A    M-3
CT1  B    M-1
CT2  B    M-1
CT2  B    M-2
CT1  B    M-2

7 rows selected.