• ベストアンサー

一定以上の行があれば最大値を持つ行を削除するSQL

MySQL5にて、あるテーブルの特定フィールドの数(つまりcount値)が一定値以上であれば、間引きのため別のフィールドの値で最大値をもつ行を削除するSQLを考案中です。 イメージとしては以下の通りですが、SQLとしては誤っており、また、冗長です。 DELETE FROM table WHERE id={$id} AND (SELECT COUNT(*) FROM table WHERE id={$id}) > 5 AND begin = (SELECT MAX(begin) FROM table WHERE id={$id}); よい知恵をお授けください。

  • MySQL
  • 回答数3
  • ありがとう数1

質問者が選んだベストアンサー

  • ベストアンサー
  • Siegrune
  • ベストアンサー率35% (316/895)
回答No.2

まず、誤認識の解消から。 補足 (2)その可能性があるため、SELECT文にはLIMIT 1の定義を追加することにします。 delete from tableA where A1 = '10000' で、tableAにA1 = '100000'のレコードが5件あると、5件とも消えます。 だからmax(A1)='10000'だったとして、 delete from tableA where A1 = (select max(A1) from tableA) としても5件消えます。たとえ下のようにlimitをつけたとしても同じ。 delete from tableA where A1 = (select max(A1) from tableA limit 0,1) さて本題のほうですが、 質問の本文を読むと、 id={$id}という指定はいらないような内容です。 delete from tableA where (id,begin) in (select id,max(begin) from tableA group by id having count(distinct begin)>5) ※idおよびmax(begin)が同一の値をもつレコードが複数あればすべて消えます。 参考:(id,begin) in については下記URL。 http://dev.mysql.com/doc/refman/5.1/ja/row-subqueries.html 参考:count(distinct begin)>5については、 http://dev.mysql.com/doc/refman/5.1-olh/ja/group-by-functions.html#function_count のCOUNT(DISTINCT expr,[expr...]) の部分。 havingは、・・・探してみて。 質問に書かれたSQLのようにid={$id}がいるということなら、 上の delete from tableA where ・・・ に and id={$id} を加えてもできるけど、 そもそも、5件以上あるIDを指定しているのでは?と思ってしまうので、 かえって効率悪いと思いますが。 (特定IDを指定するのなら、havingを使わずに select count(*) from tableA where ・・・で取得したほうが効率よい。) 注:環境ないので未検証です。

Tank2005
質問者

お礼

また一つ賢くなりました。ご回答ありがとうございます。

その他の回答 (2)

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.3

>必ず1行単位で追加するようにしている じゃ、投入してから削除するのではなくデータを投入する際に件数をカウントして 5件あるときには変更処理にすればいいですね

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.1

ちょっと質問の意図がわからないのですが 特定のidのレコードについて、レコード数が5個より多い場合 beginの値が最大のものを削除する ということなのでしょうか? そこで疑問ですが (1)仮に10個あったとしても最大のものだけ削除すると9個になりますが 命題で言う5個以上の状況はかわりません。どう判断すればよいのでしょうか? (2)beginはユニークな値なのでしょうか?仮に同じ値があった場合 複数のレコードが消えてしまいますがよろしいのでしょうか?

Tank2005
質問者

補足

(1)必ず1行単位で追加するようにしているため、いきなり10行になってしまうことはありません。 (2)その可能性があるため、SELECT文にはLIMIT 1の定義を追加することにします。

関連するQ&A

  • 別テーブルからSELECTした値を持つ行を削除するSQLは?

    削除 SQL がわかりません。 SQL-1 SELECT id FROM table2 WHERE date_in IS NULL; これで取得したidを持つレコード(別テーブル)を削除したいのですが、 どうすれば組み合わさるのでしょうか? DELETE FROM table1 WHERE id=???

  • SQL WHERE文のノットイコールについて

    初歩的な質問ですみません。 Oracle9iでsqlplusで接続しています。 「member_accountテーブルのdelete_flag(VARCHAR2)カラムの値が'1'ではない」ものを抽出したいと考えてます。 ただ、正しい結果が返ってきません。 何が問題でしょうか?よろしくお願いいたします。 ============================================================ SQL> select count(ID) from member_account; COUNT(ID) ---------- 16450 SQL> SQL> select count(ID) from member_account where delete_flag='1'; COUNT(ID) ---------- 107 SQL> SQL> select count(ID) from member_account where delete_flag<>'1'; COUNT(ID) ---------- 0 SQL> SQL> select count(ID) from member_account where delete_flag!='1'; COUNT(ID) ---------- 0 ============================================================ よろしくお願いいたします。

  • PL/SQLのDELETE文について

    PL/SQLでDELETE文を書こうとしているのですが、 文法がわかりません。。。 分かる方がおられましたら、教えてくださいm(_ _)m 今、TABLE1を削除したいのですが、条件がいろいろあって、 以下のように書いてみたのですがダメでした。 こういう書き方は、できないんでしょうか・・・。 削除条件は、TABLE2に存在し、かつ、TABLE2のTENSUが0のもので、 TABLE3が存在しないものです。 DELETE TABLE1 FROM TABLE1 ,TABLE2 ,TABLE3 WHERE TABLE1.ID = TABLE2.ID AND TABLE2.TENSU = 0 AND Not Exists (SELECT TABLE3.ID FROM TABLE3 WHERE TABLE3.ID = TABLE2.ID) 説明が下手なので、うまく、伝わっているか、心配なのですが・・・、 よろしくお願いします。

  • 文字例の比較について WHERE句

    文字例の比較について、教えてください。 DELETE_FLAGという項目には、文字列の"1"、もしくはNULLが入っています。 セレクト文でDELETE_FLAGが"1"以外のものを抽出したいのですが、うまくいきません。 以下のSQL文を実行した際に、結果として5件ヒットしてほしいのですが、なぜか0件となってしまいます。 select count(product_id) from hpk_atoz_list_view where delete_flag != '1'; 原因わかりますでしょうか? よろしくお願いいたします。 ============================================================================ SQL> select delete_flag, product_id from product_list_view; DELETE_FLAG product_ID ---------------------------------   0002 1 1003   0002 1 1003   0002 1 1003   0002   0002 8行が選択されました。 SQL> SQL> select count(product_id) from product_list_view where delete_flag != '1'; COUNT(product_id) ---------- 0 SQL> SQL> select count(product_id) from product_list_view where delete_flag = '1'; COUNT(product_id) ---------- 3 SQL> SQL> select count(product_id) from product_list_view where delete_flag is not null; COUNT(product_id) ---------- 3 SQL> SQL> select count(product_id) from product_list_view where delete_flag is null; COUNT(product_id) ---------- 5 SQL> =====以上=====

  • SQL文 この部分はなんていうのでしょう?

    SELECT T_テーブル1.フィールド1 FROM T_テーブル1 ORDER BY T_テーブル1.フィールド1; なら、 ”ORDER BY句” SELECT T_テーブル1.フィールド1 FROM T_テーブル1 WHERE (((T_テーブル1.フィールド1)="A")); なら ”WHERE句” と言いますが、 DELETE Table1.* FROM Table1; や UPDATE テーブル1 Set テーブル1.フィールド1 = "い" WHERE (((テーブル1.フィールド1)=”あ")); や DROP Table Table1; の deleteやupdateやdropの部分は何て言うのでしょうか? そのまま、SELECT句DELETE句などというのでしょうか?

  • SQLインジェクションの対策

    SQLインジェクションの対策 いつもお世話になっております。 SQLインジェクションの対策についてお伺いいたします。 もともと↓のようなSQL文だったものを "select user_id from table where user_id='{$user_id}'" 以下のように変更しました。 "select user_id from table where user_id='" . mysql_real_escape_string($user_id) . "'" 以下のように実行されていたSQL文は select user_idfrom table where user_id='10001' and 'a'='a' ↓のようにエスケープ処理して実行されるようになりました。(入力値は「10001' and 'a'='a」) select user_id from table where user_id='10001\' and \'a\'=\'a' ですが、phpMyAdminで実行してみるとどちらのSQL文も同じ結果が取得できてしまいます。 これでは対策になっていないと思ったので、質問させていただきました。 (magic_quotes_gpcはoffに設定しています。) なにか他の方法がいいのでしょうか。 ご教示よろしくお願いいたします。 <環境> PHP 5.1.6 MySQL 5.0.45

    • ベストアンサー
    • PHP
  • グループ中で最大値を含む行を抽出するSQLはどう書けば良いでしょうか

    列A,Bでグループ化し、グループ中で列Cが最大値の列を取得したいのです。 数百万件あるので極力低コストにしたく、 副問合せはINDEXが効かないと聞いたのですが、 それ以外思いつきませんでした。 副問合せしない方法はないでしょうか? また、副問合せの中でコストの低いSQLはどんなものでしょうか。 ■テーブルです 名前 型 CUST_ID CHAR(8) BRANCH_ID CHAR(4) VALID_DATE DATE MYDATA VARCHAR2(20) CUST_ID BRAN VALID_DA MYDATA 19740704 0000 06-01-01 comment0001 19740704 0000 05-12-01 comment0000 19740704 0000 06-02-01 comment0002 19740704 0001 06-01-01 comment0011 19740704 0001 06-03-01 comment0012 ■このような結果が欲しいのですが。。。 CUST_ID BRAN VALID_DA MYDATA 19740704 0000 06-02-01 comment0002 19740704 0001 06-03-01 comment0012 ■考えたSQLです(検証済) select a.cust_id,a.branch_id,a.valid_date,a.mydata from test a,(select cust_id,branch_id,max(valid_date) as mvd from test group by cust_id,branch_id) b where a.cust_id=b.cust_id and a.branch_id=b.branch_id and a.valid_date=b.mvd ; select a.cust_id,a.branch_id,a.valid_date,a.mydata from test a where not exists (select * from test b where a.cust_id=b.cust_id and a.branch_id < b.branch_id) ; select cust_id,branch_id,valid_date,mydata from test where (cust_id,branch_id,valid_date) in (select cust_id,branch_id,max(valid_date) from test group by cust_id,branch_id) ; select cust_id,branch_id,valid_date,mydata from (select cust_id,branch_id,valid_date,mydata,max(valid_date) over(partition by cust_id,branch_id) as mvd from test) where valid_date=mvd ;

  • 削除した件数を取得

    下記のようにSQLを実行したのですがエラーになりました。そもそもDELETEでCOUNT()を使用することは可能なのでしょうか。 DELETE COUNT(*) FROM table_data WHERE serial = 'DoCoMo';

    • ベストアンサー
    • MySQL
  • TABLE内の行をランダムに半減させたい。

    あるテーブル(table)の行をランダムに半減させたいと思っています。 このテーブルのキーは、instance_idとuser_idです。 つまり、同一userが複数のinstanceを持ち、同一のinstanceが複数のuserに所持されうるというテーブル構成になっています、 今、行数は200あるとしますと、 delete from table where insance_id IN (select insance_id from table order by random() limit 100) としますと、tableの行が半減するわけではなく、同一instanceが複数userに所持されますので、もっと行数が減ってしまいます。 そこで、 # delete from table where instance_id AND user_id IN (select instance_id , user_id from table order by random() limit 100); というようなことをしたのですが、 ERROR: subquery has too many columns と出てしまいます。 2時間ほど、Webで調べて頑張ったのですが、よくわからないので、 これを実現するSQL文をお教えいただけませんか。 どうか、よろしくお願いいたします。

  • OR と IN の違い

    いつもお世話になります。 SQLについて質問です。 以下の2つのSQLがあります。同じ結果が得られると思ったのですが、 結果がまたくことなりました。何故か理由を教えて下さい。 (1) SELECT count(*) FROM uriage WHERE subsystem_cd = 'ABC' AND store_name = 'MISAWA' AND opend_dt = 20061130 or opten_dt = 19000101 AND id <> 108 AND begin_dt <= 20061122 (2) SELECT count(*) FROM uriage WHERE subsystem_cd = 'ABC' AND store_name = 'MISAWA' AND opend_dt in (20061130,19000101) AND id <> 108 AND begin_dt <= 20061122 ※(1)のorのところを1つずつにして実行(SQL文を2つ作成)し、その結果を合計すると(2)の結果と同等になります。