SQLで重複データを一つだけ残して削除する

2012.02.03.金
技術挨拶・自己紹介

こんにちは、常角です。
というか、初投稿なのではじめまして、です。

名前は、常角(つねずみ)と読みます。
田舎出身で、地元ではそこそこありふれた名前ですが、神戸に出てきてからは、弟たち以外に同じ苗字の人間に会ったことはないです。
加えて、大体初対面の人は「つねかどさん」と呼ぶので、毎回突っ込むのが億劫になります。(宅配の人とかに対しては、もう諦めています)

さて、記念すべき初投稿のネタなのですが・・・何にしよう。
考えてなかったので、書くことがないです。

では格好つかないので、以前仕事で使った、便利だと思ったSQL文を紹介します。
どんな事をするのかは、タイトルを見てね。
少しだけどういうことか説明すると、
DB上で、特定の値が重複しているデータが存在する場合、それを一つだけ残して、残りを削除するためには?という事です。

Delete t1
From 削除したいデータのテーブル t1
Where t1.IDなど数値系のフィールド(以下、【ID】とします) > (
Select Min(t2.ID)
From 削除したいデータのテーブル t2 (t1と同じテーブル)
Where
t1.重複の条件となるフィールドA = t2.重複の条件となるフィールドA
And (t1.重複の条件となるフィールドB = t2.重複の条件となるフィールドB
or (t1.重複の条件となるフィールドB Is Null And t2.重複の条件となるフィールドB Is Null)) ・・・ ※
And …
Having Count(*) > 1
)

t2のテーブルのほうで、重複したデータの一覧が取得されて、
t1のほうのWhere文の所で、そのうち1件だけを除外してくれるので、重複データの2件目以降だけを削除できます。
逆に、
Where t1.IDなど数値系のフィールド(以下、【ID】とします) > (

Where t1.IDなど数値系のフィールド(以下、【ID】とします) = (
に変えると、重複データを1件だけ取得してきますので、Select文などで、重複データを無視したデータの一覧を取得する時に便利です。

また、Where句で使うフィールドが、Nullを許可しているフィールドの場合、「=」だときちんと判定してくれないので※の部分のように、Is Nullをやってやらないとダメです。

IDの部分に使うフィールドは、ユニークな値を持っていて、必ず値が入力されているフィールドであれば、なんでもいいです。
そういった類の値が存在しない場合は、RowNum(ORACLE)で、番号を振ってあげればOKです。
⇒SQLServerの場合は、Row_Number
 MySQLにはこういったものがないようなので、
 set @row_num = 0;
 select (@row_num:=@row_num+1) as row_number, col1
 from tbl1;
 といった具合に自前でつけてやらねばいけません。
但し、これを使うと処理が半端なく重くなるので、1000件程度ならいいけど、1万10万になると、数十分数時間、それ以上になる可能性があるので、あまりお勧めできないです。
なので、DBテーブルを作るときには、ユニークIDを一つつけてやりましょう。
他、残す1件をどれにするのかは、最初にきちんと決めておかないと泣きをみるハメになるかもしれないので、その辺は注意しましょう。

・・・・・・

SQLって、勉強し始めた頃は何がなにやらさっぱりわからなかったんですが、理解し始めてくると、パズルみたいで面白いですよね?(僕だけ?)
複雑な抽出がうまくいった時とか、おもわず、「よっしゃ!」ってなったり。
まあ、それはSQLに限った話ではないんですけどね。

これからも、ちょくちょく書いていくことになる・・・と思いますので、その時はまたお付き合いいただけるとありがたいです。
次書くのはJavaかPHPか、あるいはAndroidか・・・あるいはそういった技術的なことから離れるのか。
まあ何かはわかりませんが。。。