9-46 順序無視で重複チェック(各値の重複あり)

SQLパズル

StringTable
ID  ColA  ColB  ColC
--  ----  ----  ----
 1    A     A     A   ←出力対象
 2    A     A     B
 3    A     A     C
 4    A     B     A
 5    A     B     B
 6    A     B     C   ←出力対象
 7    A     C     A
 8    A     C     C
 9    B     A     A
10    B     A     B
11    B     B     A
12    B     B     B   ←出力対象
13    B     B     C
14    B     C     B
15    B     C     C
16    C     A     A
17    C     A     C
18    C     B     B

ColAとColBとColCの順序を無視した組み合わせで、
重複が存在しないレコードを出力する

9-37 順序無視でグループ化(2列)のアレンジです

データ作成スクリプト

create table StringTable(
ID   number(2) not null,
ColA char(1)   not null,
ColB char(1)   not null,
ColC char(1)   not null);

insert into StringTable values( 1,'A','A','A');
insert into StringTable values( 2,'A','A','B');
insert into StringTable values( 3,'A','A','C');
insert into StringTable values( 4,'A','B','A');
insert into StringTable values( 5,'A','B','B');
insert into StringTable values( 6,'A','B','C');
insert into StringTable values( 7,'A','C','A');
insert into StringTable values( 8,'A','C','C');
insert into StringTable values( 9,'B','A','A');
insert into StringTable values(10,'B','A','B');
insert into StringTable values(11,'B','B','A');
insert into StringTable values(12,'B','B','B');
insert into StringTable values(13,'B','B','C');
insert into StringTable values(14,'B','C','B');
insert into StringTable values(15,'B','C','C');
insert into StringTable values(16,'C','A','A');
insert into StringTable values(17,'C','A','C');
insert into StringTable values(18,'C','B','B');
insert into StringTable values(19,'C','B','C');
commit;

SQL

with WorkView as (
select ID,ColA,ColB,ColC,
decode(ColA,ColB,1,0)+decode(ColA,ColC,1,0) as ColAC,
decode(ColB,ColA,1,0)+decode(ColB,ColC,1,0) as ColBC,
decode(ColC,ColA,1,0)+decode(ColC,ColB,1,0) as ColCC
from StringTable)
select ID,ColA,ColB,ColC
  from WorkView a
 where not exists(select 1 from WorkView b
                   where a.RowID != b.RowID
                     and (a.ColA,a.ColAC) in((b.ColA,b.ColAC),(b.ColB,b.ColBC),(b.ColC,b.ColCC))
                     and (a.ColB,a.ColBC) in((b.ColA,b.ColAC),(b.ColB,b.ColBC),(b.ColC,b.ColCC))
                     and (a.ColC,a.ColCC) in((b.ColA,b.ColAC),(b.ColB,b.ColBC),(b.ColC,b.ColCC))
                     and (b.ColA,b.ColAC) in((a.ColA,a.ColAC),(a.ColB,a.ColBC),(a.ColC,a.ColCC))
                     and (b.ColB,b.ColBC) in((a.ColA,a.ColAC),(a.ColB,a.ColBC),(a.ColC,a.ColCC))
                     and (b.ColC,b.ColCC) in((a.ColA,a.ColAC),(a.ColB,a.ColBC),(a.ColC,a.ColCC)));

解説

各値の個数を求めてから、
包含関係を調べてます

数学の集合理論の、
集合Aが集合Bの部分集合で、かつ
集合Bが集合Aの部分集合ならば、
集合Aと集合Bは等しい
(A=B ⇔ A⊂B かつ B⊂A)
を使ってます

各値に重複が存在しない場合は、
7-41 順序無視の重複チェック(各値の重複なし)
の方法でいいです

---