10-141 重複が存在するグループを出力

SQLパズル

IDTable
ID  Seq  Val1  Val2  Val3  Val4
--  ---  ----  ----  ----  ----
 1    1   100   200   300   300  ←出力対象
 1    2   100   200   400   500  ←出力対象
 1    3   300   500   700   800  ←出力対象
 1    4   500   600   700   800  ←出力対象
 1    5   500   600   700   800  ←出力対象
 2    1   100   200   300   300
 2    2   100   200   400   500
 2    3   300   500   700   800
 2    4   500   600   700   800
 2    5   800   800   800   800
 3    1   100   200   300   400
 4    1   200   600   700   900
 4    2   900   700   600   200
 5    1   300   500   700   800  ←出力対象
 5    2   300   500   700   800  ←出力対象
 5    3   400   600   800   900  ←出力対象
 6    1   600   700   800   900  ←出力対象
 6    2   800   800   800   900  ←出力対象
 6    3   800   800   800   900  ←出力対象
 6    4   900   100   200   300  ←出力対象

IDごとで、
(Val1,Val2,Val3,Val4)が重複するレコードが存在するIDの、
全レコードを出力するクエリは、

2回スキャンする方法だと、こんなのがあります

select ID,Seq,Val1,Val2,Val3,Val4
  from IDTable a
 where ID in (select ID
                from IDTable b
               group by ID,Val1,Val2,Val3,Val4
              having count(*) >= 2);

分析関数を使って、1回のスキャンで行う方法を考えてみてください

データ作成スクリプト

create table IDTable(
ID   number(1),
Seq  number(1),
Val1 number(3) not null,
Val2 number(3) not null,
Val3 number(3) not null,
Val4 number(3) not null,
primary key(ID,Seq));

insert all
into IDTable values(1,1,100,200,300,300)
into IDTable values(1,2,100,200,400,500)
into IDTable values(1,3,300,500,700,800)
into IDTable values(1,4,500,600,700,800)
into IDTable values(1,5,500,600,700,800)
into IDTable values(2,1,100,200,300,300)
into IDTable values(2,2,100,200,400,500)
into IDTable values(2,3,300,500,700,800)
into IDTable values(2,4,500,600,700,800)
into IDTable values(2,5,800,800,800,800)
into IDTable values(3,1,100,200,300,400)
into IDTable values(4,1,200,600,700,900)
into IDTable values(4,2,900,700,600,200)
into IDTable values(5,1,300,500,700,800)
into IDTable values(5,2,300,500,700,800)
into IDTable values(5,3,400,600,800,900)
into IDTable values(6,1,600,700,800,900)
into IDTable values(6,2,800,800,800,900)
into IDTable values(6,3,800,800,800,900)
into IDTable values(6,4,900,100,200,300)
select 1 from dual;

SQL

--■■■逆ソートを使う方法■■■
select ID,Seq,Val1,Val2,Val3,Val4
from (select ID,Seq,Val1,Val2,Val3,Val4,
      count(*) over(partition by ID) as RecordCount,
      dense_rank() over(partition by ID order by Val1,Val2,Val3,Val4) as Rank,
      dense_rank() over(partition by ID order by Val1 desc,Val2 desc,Val3 desc,Val4 desc) as RevRank
      from IDTable)
where RecordCount+1 > Rank + RevRank;

--■■■逆ソートを使わない方法■■■
select ID,Seq,Val1,Val2,Val3,Val4
from (select ID,Seq,Val1,Val2,Val3,Val4,max(C) over(partition by ID) as MaxC
        from (select ID,Seq,Val1,Val2,Val3,Val4,
              count(*) over(partition by ID,Val1,Val2,Val3,Val4) as C
              from IDTable))
where MaxC >= 2;

解説

Oracleでの、
逆ソートの有効活用法と言えるかもしれませんね

---