この記事は重複行を一覧にして表示する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のバージョンによってはうまく動かない時があります。
そのほか、「こういう書き方でも実現できる！」とか、「あれ？うまく動かないよ？」とかありましたら、
ご意見、ご指導のほどよろしくお願いいたします。
では、また気が向いたときに書きます。