MySQL


カラム job に work_at_hatena が入っているレコードが5件以上在れば最新の五件以外全て削除するSQLを書いて下さい。
ちなみにPHPなどでひとつひとつ処理するのではなく、SQL一文で実現する方法のみを教えて下さい。

ver : 最新

回答の条件
  • 1人2回まで
  • 登録:2009/05/06 23:35:35
  • 終了:2009/05/13 23:40:03

ベストアンサー

id:chuken_kenkou No.6

chuken_kenkou回答回数722ベストアンサー獲得回数542009/05/07 18:42:54

ポイント18pt

他表を使ったdelete、自表を使ったdeleteは、RDBMSにより仕様差や制限がある部分です。

例えば、

  1. 他表を利用するのに、from句の使い方の違い
  2. MySQLでは、ver 5.1でも、delete対象の表をサブクエリなどで使用できない
  3. 上記は、ビュー表などを媒介すると、制限がかかっていない
  4. ビュー表はMySQL 5.0で実装されたが、ビュー表中ではインラインビューを使えない
  5. limit句の使い方に、サブクエリでは制限がある(結果が1行でないとだめ)

などです。

ビューを使って実現する例を示します。

なお、

  1. nameという列があり、name毎にjob='work_at_hatena'のidで、auto_incrementが昇順で6件目以降を削除

といった例にしておきます。

0.準備

(1)表定義

create table t1
(id          int primary key auto_increment,
 name        varchar(10),
 job         varchar(30),
 dttm        timestamp);

(2)テストデータ

truncate table t1;

insert into t1(name,job) values
('a','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena');

1.ビュー表の定義

create view vt1
as
select
  x.id,x.name,x.job,
  count(*) as rcnt
 from t1 as x,t1 as y
 where x.job='work_at_hatena'
   and x.name=y.name
   and x.id>=y.id
 group by x.id,x.name,x.job

2.削除

delete t1 from t1,vt1
 where t1.id=vt1.id
   and rcnt>5

その他の回答(5件)

id:frkw2004 No.1

ふるるP回答回数192ベストアンサー獲得回数212009/05/07 11:59:37

ポイント19pt

テーブルのカラムを(Key, jon)とします。Keyは主キーでオートナンバーとします(ですから数値が大きいほうが新しいレコードです)。テーブル名はwkとします。

delete From wk

Where

KEY In (

SELECT key

FROM wk A

where job like '%work\_at\_hatena%'

and

(Select count(*) From wk where key>A.key and job like '%work\_at\_hatena%')>=5

)

でどうでしょうか。

id:esecua

NGです。

例えば以下のような

delete from hatena where id in (

select id from hatena where job='work_at_hatena'

order by id desc limit 10

offset 5);

SQLがベストなのですが、以上のSQLは動かないのです、、、

アドバイスを。

2009/05/07 14:46:04
id:b-wind No.2

b-wind回答回数3344ベストアンサー獲得回数4402009/05/07 15:55:59

ポイント19pt
DELETE FROM hatena AS old
  WHERE old.job='work_at_hatena'
   AND
   ( SELECT count(*) FROM hatena AS new WHERE new.job='work_at_hatena' AND new.id > old.id ) > 5

こんなところ?

細かく確認してないから、不等号は逆かもしれんねぇ。

MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.1 DELETE 構文

id:esecua

うごかないです。

2009/05/07 18:38:41
id:frkw2004 No.3

ふるるP回答回数192ベストアンサー獲得回数212009/05/07 16:49:14

ポイント18pt

MySQL は使ったことが無いのですけど、インラインビューの中でOrder By は使えないんじゃないかと。

私が提示したのは相関サブクエリですので、

delete From HATENA

Where id In (

SELECT id FROM HATENA A

where job ='work_at_hatena'

and

(Select count(*) From wk

where key>A.key

and job = 'work_at_hatena')>=5

)

となって、Order By は使わないのですが、エラーがでたらどんなエラーですか?

MySQL の仕様として使えないSQLだったら申し訳ない。

id:esecua

wk?

2009/05/07 18:46:00
id:hanabc No.4

hanabc回答回数20ベストアンサー獲得回数42009/05/07 16:43:22

ポイント18pt

コマンドラインで実行すると、こういうエラーが出るケースでしょうか。

> ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'


格好悪いですが、こういうSQLではどうでしょう。

手元の環境では動作しました。


delete from hatena where id in (

select id from (

select id from hatena

where job='work_at_hatena'

order by id desc limit 10 offset 5

) a

);

id:esecua

ありがとうございます。

うん〜1日に4万回SQLを実行するのでなるべくSELECT等を減らしたものがベストです。

2009/05/07 18:37:08
id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542009/05/07 19:05:49

ポイント18pt

うん〜1日に4万回SQLを実行するのでなるべくSELECT等を減らしたものがベストです。

InnoDBでなくMyISAMであれば、auto_increment列をprimary keyの2番目以降に定義するという方法があります。



create table t1
(id          int auto_increment,
 job         varchar(30),
 primary key(job,id)
)

こうしておけば、次のようにシンプルになります。

delete from t1
 where job='work_at_hatena'
   and id>5
id:esecua

not workingです。

2009/05/08 01:17:44
id:chuken_kenkou No.6

chuken_kenkou回答回数722ベストアンサー獲得回数542009/05/07 18:42:54ここでベストアンサー

ポイント18pt

他表を使ったdelete、自表を使ったdeleteは、RDBMSにより仕様差や制限がある部分です。

例えば、

  1. 他表を利用するのに、from句の使い方の違い
  2. MySQLでは、ver 5.1でも、delete対象の表をサブクエリなどで使用できない
  3. 上記は、ビュー表などを媒介すると、制限がかかっていない
  4. ビュー表はMySQL 5.0で実装されたが、ビュー表中ではインラインビューを使えない
  5. limit句の使い方に、サブクエリでは制限がある(結果が1行でないとだめ)

などです。

ビューを使って実現する例を示します。

なお、

  1. nameという列があり、name毎にjob='work_at_hatena'のidで、auto_incrementが昇順で6件目以降を削除

といった例にしておきます。

0.準備

(1)表定義

create table t1
(id          int primary key auto_increment,
 name        varchar(10),
 job         varchar(30),
 dttm        timestamp);

(2)テストデータ

truncate table t1;

insert into t1(name,job) values
('a','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('b','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('c','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena'),
('d','work_at_hatena');

1.ビュー表の定義

create view vt1
as
select
  x.id,x.name,x.job,
  count(*) as rcnt
 from t1 as x,t1 as y
 where x.job='work_at_hatena'
   and x.name=y.name
   and x.id>=y.id
 group by x.id,x.name,x.job

2.削除

delete t1 from t1,vt1
 where t1.id=vt1.id
   and rcnt>5
  • id:b-wind
    > ver : 最新
    いつの最新か分からんので正確に書いてください。

    > 最新の五件
    「最新」と判断する条件は?
  • id:esecua
    Ver 5.1

    >「最新」と判断する条件は?

    autoincrement で id を全レコードにつけていますので、offsetで判断できるとおもいます。
  • id:chuken_kenkou
    #5、#6と回答しました。

    #5では、「他表や自表を用いたdeleteには、RDBMSによる仕様差、制限などが多い」とした上で、MySQL 5.0以降なら使えるビュー経由でdeleteする例を提示しました。

    #6では、

    >うん〜1日に4万回SQLを実行するのでなるべくSELECT等を減らしたものがベスト

    という#4への返答から、「auto_increment列を、primary key構成列の二番目以降とする」といった方法を提示しました。
    しかし、deleteで歯抜けができた場合には、触れていませんでした。

    性能を出したいなら、変動が大きい「行数」で操作を変えたり、deleteは発生させない方がいいでしょう。
    削除フラグ等を設け、updateで擬似削除する方が性能は出せるかも知れません。

  • id:frkw2004
    すみません。 wkじゃなくてテーブル名はHATENAでしたね。
    わからないことは無いと思いますが、
    delete From HATENA
    Where id In (
    SELECT id FROM HATENA A
    where job ='work_at_hatena'
    and
    (Select count(*) From HATENA
    where key>A.key
    and job = 'work_at_hatena')>=5
    )
    ではどんなエラーがでたのでしょうか? 普通に相関サブクエリが使えるならこれでいけると思います。
    Accessではこれでいけました。
    b-wind さんが提示した考え方と同じです。

    1日に4万回実行する、という運用が変更できればベターな気がしますね。実削除ではなく、削除フラグを作っておいて、夜間バッチで実削除するとか。
  • id:esecua
    frkw2004さん、どうもです。

    エラーは以下の通りです。

    #1093 - You can't specify target table 'hatena' for update in FROM clause

    あと、keyって私の例で言うidですよね?確認。

  • id:chuken_kenkou
    frkw2004さんのdelete文を、MySQL用の構文にしてみました。

    t1の部分は、HATENAなど、お使いの表名に変えてください。

    delete t1
    From t1, -- 削除対象の表から検索
    (select id from t1 as A
    where job ='work_at_hatena'
    and (Select count(*) From t1
    where id<A.id
    and job = 'work_at_hatena')>=5
    ) as x
    where t1.id=x.id; -- 列名は、from句で指定した表名、別名で列名を修飾
  • id:chuken_kenkou
    >質問者:esecua 2009-05-08 01:17:44
    >not workingです。

    これは、どういう意味でしょうか?
    InnoDBを使うという意味でしょうか?
    それとも、「primary key(job,id)」とできないという意味ですか?

    http://dev.mysql.com/doc/refman/4.1/ja/example-auto-increment.html

    に記載があるように、「idがauto_incrementで、primary key(job,id)」としておけば、「jobの値毎に、idには1からの通番が付与」されます。ただ、削除で歯向けになることもあり、その辺の工夫が必要です。そうできれば、

    delete from t1
    where job='work_at_hatena'
    and id>5

    といったシンプルなdelete文にできます。



    esecuaさんの
    「例えば以下のようなようにやりたいが、動かない」

    delete from hatena where id in (
    select id from hatena where job='work_at_hatena'
    order by id desc limit 10
    offset 5);


    これをMySQLで動く構文に変えてみました。
    「t1」としている部分を「hatena」など、お使いの表名に変更してください。

    delete t1 -- 削除対象の表
    from t1, -- 削除対象の表を、検索にも使う
    (select id from t1  -- 削除対象の表を、検索にも使う  
    where job='work_at_hatena'
    order by id desc limit 10 offset 5) as x
    where t1.id=x.id; -- from句で指定した表の結合条件

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません