5-41 重複値を持つ行を取得

SQLパズル

Table541テーブル
ID  Col1  Col2
--  ----  ----
 1    20     0
 2    10     2
 3    11     1
 4    12     5
 5    14     5
 6    15     3
 7    15     4
 8    15     6

Col1が、他の行のCol1と等しい行、
もしくは、
Col2が、他の行のCol2と等しい行
を出力する。

出力結果
ID  Col1  Col2
--  ----  ----
 4    12     5
 5    14     5
 6    15     3
 7    15     4
 8    15     6

データ作成スクリプト

create Table Table541(
ID   number(1),
Col1 number(2),
Col2 number(1));

insert into Table541 values(1,20,0);
insert into Table541 values(2,10,2);
insert into Table541 values(3,11,1);
insert into Table541 values(4,12,5);
insert into Table541 values(5,14,5);
insert into Table541 values(6,15,3);
insert into Table541 values(7,15,4);
insert into Table541 values(8,15,6);
commit;

SQL

--■■■existsを使う方法■■■
select ID,Col1,Col2 from Table541 a
where exists(select 1 from Table541 b
              where b.RowID != a.RowID
                and (b.Col1=a.Col1 or b.Col2=a.Col2))
order by ID;

--■■■分析関数を使う方法1■■■
select ID,Col1,Col2
from(select ID,Col1,count(Col1) over(partition by Col1) as Col1Count,
               Col2,count(Col2) over(partition by Col2) as Col2Count
       from Table541)
where greatest(Col1Count,Col2Count) >= 2
order by ID;

--■■■分析関数を使う方法2■■■
select ID,Col1,Col2
from(select ID,Col1,count(Col1) over(partition by Col1) as Col1Count,
               Col2,count(Col2) over(partition by Col2) as Col2Count
       from Table541)
where 2 <= any(Col1Count,Col2Count)
order by ID;

解説

分析関数を使う方法では、
パーテーションを切った中でのcountを取得して、
値が等しい行の数を取得してます。