【中級者向け】SQLで重複行を一覧表示する

この記事は重複行を一覧にして表示するSQLの記事です。
※なお、本記事はSQLServerで動作確認、Oracleでも動くと思われます。
目次
１．やりたいこと
２．実行SQL
３．重複行が3つ以上の場
４．ロジックの簡単な説明
５．感想

やりたいこと

例えば、下記のようなテーブルがあったとします。

テーブル名：[商品マスタ]

ID	商品	産地	品種
1	りんご	青森	つがる
2	りんご	青森	つがる
3	りんご	青森	つがる
4	りんご	長野	紅玉
5	みかん	和歌山	温州
6	みかん	愛媛	温州
7	みかん	愛媛	八朔
8	みかん	和歌山	温州
9	みかん	和歌山	ぽんかん

このテーブルにおいてID1とID2、ID5とID8は内容が重複しています。
これを、こちらのSQLで重複しているIDを下表のように一覧表示できます。

| 商品 | 産地 | 品種 |重複数 |重複ID1 |重複ID2 |重複ID3 |
| ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| りんご | 青森 | つがる　|　2　|　1　|　2 |　3 |
| みかん | 和歌山 | 温州 |　2　|　5　|　8 |
| りんご | 長野 |　紅玉 |　1　|　4　|
| みかん | 愛媛 |　温州　|　1　|　6　|
| みかん | 愛媛 |　八朔　|　1　|　7　|
| みかん | 和歌山 |　ぽんかん　|　1　|　9　|

実行

select [商品],[産地],[品種]
       ,count([商品]) as 重複数
       ,min(case tmp.seq when 1 then tmp.[ID] else null end) as 重複ID1
       ,min(case tmp.seq when 2 then tmp.[ID] else null end) as 重複ID2
       ,min(case tmp.seq when 3 then tmp.[ID] else null end) as 重複ID3
From
(
select [ID],[商品],[産地],[品種]
       ,row_number()over (partition by [商品],[産地],[品種]
       order by [ID]) as seq
from [商品マスタ]
) tmp
group by [商品],[産地],[品種]
order by count([商品])
       ,min(case tmp.seq when 1 then tmp.[ID] else null end)
       ,min(case tmp.seq when 2 then tmp.[ID] else null end)
       ,min(case tmp.seq when 3 then tmp.[ID] else null end)
;

使用上の注意点
・上記SQLは最大の重複数が3件までです。4,5件と増えていく場合がSELECT句とORDER_BY句の行を追記してください。
　→追記する場所
・表示の順序は、重複したIDの多かった順にしています。OrderBY句を編集して、表示順を変更してください。

重複行が3つ以上の場合

下記のようにSELECT句とORDERBY句に追記していく必要があります。

select [商品],[産地],[品種]
       ,count([商品])
       ,min(case tmp.seq when 1 then tmp.[ID] else null end) as "重複ID1"
       ,min(case tmp.seq when 2 then tmp.[ID] else null end) as "重複ID2"
       ,min(case tmp.seq when 3 then tmp.[ID] else null end) as "重複ID3"
---重複行が3つ以上の場合はここに追記 -Start
       ,min(case tmp.seq when 4 then tmp.[ID] else null end) as "重複ID3"
---重複行が3つ以上の場合はここに追記 -End
From
(
select [ID],[商品],[産地],[品種]
       ,row_number()over (partition by [商品],[産地],[品種]
       order by [ID]) as seq
from [商品マスタ]
) tmp
group by [商品],[産地],[品種]
order by count([商品])
       ,min(case tmp.seq when 1 then tmp.[ID] else null end)
       ,min(case tmp.seq when 2 then tmp.[ID] else null end)
       ,min(case tmp.seq when 3 then tmp.[ID] else null end)
---重複行が3つ以上の場合はここに追記 -Start
       ,min(case tmp.seq when 4 then tmp.[ID] else null end)
---重複行が3つ以上の場合はここに追記 -End
;

ロジックの簡単な説明

ここから先はSQLの細かい記述の解説をしていきます。
興味のある人だけ見てください。
第一に、このSQLの肝はここのtmp表です。

(
select [ID],[商品],[産地],[品種]
       ,row_number()over (partition by [商品],[産地],[品種]
       order by [ID]) as seq
from [商品マスタ]
) tmp

ここでは、partition by句で商品、産地、品種の同じ行に対して順にseq列に連番を振っています・・・(;^ω^)
何言ってるかわかりませんね。
なので、表を用意しました。tmp表はこんな感じになります。

商品	産地	品種	seq
りんご	青森	つがる	1
りんご	青森	つがる	2
りんご	青森	つがる	3
りんご	長野	紅玉	1
みかん	和歌山	温州	1
みかん	和歌山	温州	2
みかん	愛媛	温州	1
みかん	愛媛	八朔	1
みかん	和歌山	ぽんかん	1

そして、この表のseq列にどの数字が入っているかをSELECT句内のCASE文で判定して、表に反映させます。
[seq]が1なら「重複ID1」、2なら「重複ID2」、3なら「重複ID3」にその時の[ID]に入れる。という風に。

       ,min(case tmp.seq when 1 then tmp.[ID] else null end) as 重複ID1
       ,min(case tmp.seq when 2 then tmp.[ID] else null end) as 重複ID2
       ,min(case tmp.seq when 3 then tmp.[ID] else null end) as 重複ID3

感想

今更な記事ではありますが、私が探した限りはなかなか出てこなかったので、載せました。
なお、SQLServerのバージョンによってはうまく動かない時があります。

そのほか、「こういう書き方でも実現できる！」とか、「あれ？うまく動かないよ？」とかありましたら、
ご意見、ご指導のほどよろしくお願いいたします。

では、また気が向いたときに書きます。