MySQLで出現回数によるDELETE

MySQLに関しての質問です。

下記のようなテーブルがあった場合

area　　name
北海道　佐々木
青森　　山田
北海道　佐藤
秋田　　鈴木
北海道　山本

テーブルの中にareaが同一のものが２つしか必要ない時に
不必要なレコードをDELETEしたいのですが
どのように処理をすれば良いのかわかりません。

SQL文だけで指定できるのか、
無理ならばどのようにするのが良いのか
ご存知の方がいらっしゃいましたら教えて下さい。
よろしくお願いします。

No.3 ベストアンサー 回答者： chukenkenkou 回答日時： 2007/03/01 12:17

>例えば、area,nameでソートして下記のような順番になったとして

>３個目以降に並んでいるレコードを削除したい

質問で提示された例が、良くないですね。
name（漢字データ）でのソートは文字コード順になるので、どれが削除されるか注意が必要ですけど、大丈夫ですか？

まず、特定の列でグループ化し、3件以上あった場合は2件だけ残し、それ以外は削除するために、各グループ内の行に、グループ内通番を付けることを考えます。

OracleやSQL Server 2005などでは、「row_number() over(～)」といった指定方法がありますが、MySQLは未サポートなので、ユーザ側で工夫する必要があります。

【グループ毎にグループ内データに通番を付けるSQL例】
select x.c1,x.c2,count(*) as rn
from t1 as x,t1 as y
where x.c1=y.c1 and x.c2>=y.c2
group by x.c1,x.c2
;

次にこの通番を利用し、通番が3以上のデータを削除することで実現できます。

【各グループにおいて、行が3件以上ある場合、2件だけ残すSQL例】
delete from w
using t1 as w,
(select x.c1,x.c2,count(*) as rn
from t1 as x,t1 as y
where x.c1=y.c1 and x.c2>=y.c2
group by x.c1,x.c2) as z
where w.c1=z.c1 and w.c2=z.c2 and rn>2
;

この回答へのお礼

教えていただいた方法でできました！

こんなやり方があるんですね。
自分で考えても無理だったと思います。
助かりました。

不明瞭な質問をしたにも関わらず
丁寧にご回答いただきまして
どうもありがとうございました。

お礼日時：2007/03/02 10:41

No.2 回答者： chukenkenkou 回答日時： 2007/03/01 02:00

MySQLのバージョンも書いてください。

この回答へのお礼

MySQLは5.0です。
よろしくお願いします。

お礼日時：2007/03/01 09:07

No.1 回答者： chukenkenkou 回答日時： 2007/03/01 01:58

質問内容が、相当に不明瞭です。

>テーブルの中にareaが同一のものが２つしか必要ない時に
>不必要なレコードをDELETEしたい

どういう意味でしょうか？
示されたデータ例でいうと、area列の値で「北海道」が3行あるので、その内、1行を削除したいということですか？

>どのように処理をすれば良いのかわかりません

どの行を削除していいのか、その条件がまったく示されていないので、これでは誰も具体的な回答ができません。
何かユニークになるキーがあって、例えば、「古いもの（or　新しいもの）を2件残す」などの条件が分からなければ、アドバイスのしようがありません。

この回答へのお礼

質問があいまいで申し訳ありませんでした。

＞示されたデータ例でいうと、area列の値で「北海道」が3行あるので、その内、1行を削除したいということですか？

そうです。
例えば、area,nameでソートして下記のような順番になったとして３個目以降に並んでいるレコードを削除したいということです。

area　　name
北海道　佐々木　（残す）
北海道　佐藤　（残す）
北海道　山本　（削除）
青森　　山田　（残す）
秋田　　鈴木　（残す）

北海道だけでなく、他のareaに関しても同様に
３個以降を削除したいと考えています。

お礼日時：2007/03/01 09:06