MySQL

Question

esecua

235

202もっと見る

110pt

コンピュータウェブ制作

MySQL

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

ver : 最新

回答の条件

1人2回まで

登録：2009/05/06 23:35:35
終了：2009/05/13 23:40:03

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

No.1

ふるるＰ194212009/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

)

でどうでしょうか。

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

No.2

b-wind33444402009/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 構文

うごかないです。

2009/05/07 18:38:41

No.3

ふるるＰ194212009/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だったら申し訳ない。

wk？

2009/05/07 18:46:00

No.4

hanabc2042009/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

);

ありがとうございます。

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

2009/05/07 18:37:08

No.5

chuken_kenkou722542009/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

not workingです。

2009/05/08 01:17:44

b-wind 2009/05/07 11:29:19

> ver : 最新
いつの最新か分からんので正確に書いてください。

> 最新の五件
「最新」と判断する条件は？
esecua 2009/05/07 13:32:29

Ver 5.1

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

autoincrement で id を全レコードにつけていますので、offsetで判断できるとおもいます。
chuken_kenkou 2009/05/07 21:11:40

#5、#6と回答しました。

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

#6では、

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

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

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

すみません。 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万回実行する、という運用が変更できればベターな気がしますね。実削除ではなく、削除フラグを作っておいて、夜間バッチで実削除するとか。
esecua 2009/05/08 01:22:57

frkw2004さん、どうもです。

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

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

あと、keyって私の例で言うidですよね？確認。
chuken_kenkou 2009/05/08 12:50:03

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句で指定した表名、別名で列名を修飾
chuken_kenkou 2009/05/09 15:08:28

>質問者：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句で指定した表の結合条件

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

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

chuken_kenkou · Accepted Answer · 2009-05-07T18:42:54+09:00

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

例えば、

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

などです。

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

なお、

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

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

０．準備

（１）表定義

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

（２）テストデータ

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');

１．ビュー表の定義

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

２．削除

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

chuken_kenkou · Accepted Answer · 2009-05-07T18:42:54+09:00

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

例えば、

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

などです。

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

なお、

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

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

０．準備

（１）表定義

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

（２）テストデータ

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');

１．ビュー表の定義

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

２．削除

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

MySQL

ベストアンサー

chuken_kenkou722542009/05/07 18:42:54

その他の回答（5件）

ふるるＰ194212009/05/07 11:59:37

b-wind33444402009/05/07 15:55:59

ふるるＰ194212009/05/07 16:49:14

hanabc2042009/05/07 16:43:22

chuken_kenkou722542009/05/07 19:05:49

chuken_kenkou722542009/05/07 18:42:54ここでベストアンサー

コメント（7件)

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