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

この記事は重複行を一覧にして表示するSQLの記事です。
※なお、本記事はSQLServerで動作確認、Oracleでも動くと思われます。
目次
１．[やりたいこと](#やりたいこと)
２．[実行SQL](#実行)
３．[重複行が3つ以上の場](#重複行が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　|

##実行

```sql
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句の行を追記してください。
　→[追記する場所](#重複行が3つ以上の場合)
・表示の順序は、重複したIDの多かった順にしています。OrderBY句を編集して、表示順を変更してください。


##重複行が3つ以上の場合
下記のようにSELECT句とORDERBY句に追記していく必要があります。

```sql

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表です。

```sql
(
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]に入れる。という風に。

```sql
       ,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のバージョンによってはうまく動かない時があります。

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

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