MySQLに関して、質問させて頂きます。
画像のようなテーブルがあります。
nameが同じレコードが多数入っており、1つを残して全て削除したいと考えています。
ただ、そのままDELETEはできず、一度どれだけの重複があるのか調査をしなければなりません。
その上で、下記を実現するSQLを組もうとしているのですが、中々上手く行きません。
●nameが同じレコードが2つ以上存在する物で、register_numが最も小さいものでは無いレコードのidを抽出したい
SELECT `id` FROM tbl GROUP BY name HAVING COUNT(*) >= 2
で抽出しましたが、これですとregister_numが**最も小さいもの__以外__**のレコードid全てを抽出する事ができません。
どなたかご教示頂けますと幸いです。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答7件
0
PostgreSQL9.5で作ってみました :-)
sql
1with tbl(name,register_num) as( 2values('大橋', 53), 3 ('大橋', 56), 4 ('大橋', 324), 5 ('田中', 1), 6 ('田中', 4), 7 ('田中', 23), 8 ('吉田', 2), 9 ('吉田', 21), 10 ('吉田',2356)) 11select * 12 from tbl a 13 where exists(select 1 from tbl b 14 where b.name = a.name 15 and b.register_num < a.register_num); 16 17 name | register_num 18------+-------------- 19 大橋 | 56 20 大橋 | 324 21 田中 | 4 22 田中 | 23 23 吉田 | 21 24 吉田 | 2356 25
投稿2016/10/07 10:24
総合スコア1147
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
sql
1SELECT `id` 2FROM `tbl` WHERE register_num <> 3(SELECT MIN(register_num) FROM tbl AS t2 WHERE tbl.name = t2.name)
投稿2016/10/07 02:19
総合スコア145184
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
sql
1SELECT DISTINCT t1.* 2FROM test AS t1 3INNER JOIN test AS t2 ON t1.name = t2.name 4 AND t1.register_num > t2.register_num 5;
ただし、上のSQLでは
「name
が同じでregister_num
が最も小さい行」
が複数ある場合、そのような行は全て抽出できません。
もし、そのような行もただ1行だけを残して削除したい場合、
以下のようにSQLを修正すると、id
が最小でない方を抽出できます。
sql
1SELECT DISTINCT t1.* 2FROM test AS t1 3INNER JOIN test AS t2 ON t1.name = t2.name 4 AND (t1.register_num > t2.register_num 5 OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 6;
抽出した行を削除する場合は、SELECT DISTINCT t1.*
をDELETE t1
に変更してください。
sql
1mysql> CREATE TABLE test ( 2 -> id int PRIMARY KEY AUTO_INCREMENT, 3 -> name varchar(8), 4 -> register_num int 5 -> ); 6Query OK, 0 rows affected (0.01 sec) 7 8mysql> INSERT INTO test (name, register_num) VALUES 9 -> ('田中', 1), 10 -> ('大橋', 53), 11 -> ('吉田', 21), 12 -> ('田中', 23), 13 -> ('田中', 4), 14 -> ('吉田', 2), 15 -> ('大橋', 324), 16 -> ('大橋', 56), 17 -> ('吉田', 2356), 18 -> ('吉田', 2); 19Query OK, 10 rows affected (0.01 sec) 20Records: 10 Duplicates: 0 Warnings: 0 21 22mysql> SELECT DISTINCT t1.* 23 -> FROM test AS t1 24 -> INNER JOIN test AS t2 ON t1.name = t2.name 25 -> AND t1.register_num > t2.register_num 26 -> ; 27+----+--------+--------------+ 28| id | name | register_num | 29+----+--------+--------------+ 30| 4 | 田中 | 23 | 31| 5 | 田中 | 4 | 32| 7 | 大橋 | 324 | 33| 8 | 大橋 | 56 | 34| 9 | 吉田 | 2356 | 35| 3 | 吉田 | 21 | 36+----+--------+--------------+ 376 rows in set (0.00 sec) 38 39mysql> SELECT DISTINCT t1.* 40 -> FROM test AS t1 41 -> INNER JOIN test AS t2 ON t1.name = t2.name 42 -> AND (t1.register_num > t2.register_num 43 -> OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 44 -> ; 45+----+--------+--------------+ 46| id | name | register_num | 47+----+--------+--------------+ 48| 4 | 田中 | 23 | 49| 5 | 田中 | 4 | 50| 7 | 大橋 | 324 | 51| 8 | 大橋 | 56 | 52| 9 | 吉田 | 2356 | 53| 3 | 吉田 | 21 | 54| 10 | 吉田 | 2 | 55+----+--------+--------------+ 567 rows in set (0.00 sec) 57 58mysql> DELETE t1 59 -> FROM test AS t1 60 -> INNER JOIN test AS t2 ON t1.name = t2.name 61 -> AND t1.register_num > t2.register_num 62 -> ; 63Query OK, 6 rows affected (0.00 sec) 64 65mysql> SELECT * FROM test; 66+----+--------+--------------+ 67| id | name | register_num | 68+----+--------+--------------+ 69| 1 | 田中 | 1 | 70| 2 | 大橋 | 53 | 71| 6 | 吉田 | 2 | 72| 10 | 吉田 | 2 | 73+----+--------+--------------+ 744 rows in set (0.00 sec) 75 76mysql> DELETE t1 77 -> FROM test AS t1 78 -> INNER JOIN test AS t2 ON t1.name = t2.name 79 -> AND (t1.register_num > t2.register_num 80 -> OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 81 -> ; 82Query OK, 1 row affected (0.00 sec) 83 84mysql> SELECT * FROM test; 85+----+--------+--------------+ 86| id | name | register_num | 87+----+--------+--------------+ 88| 1 | 田中 | 1 | 89| 2 | 大橋 | 53 | 90| 6 | 吉田 | 2 | 91+----+--------+--------------+ 923 rows in set (0.00 sec)
投稿2016/10/07 02:58
総合スコア4791
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
以下でどうでしょう。
SQL
1Select id FROM tbl a 2WHERE 3Exists (SELECT * FROM tbl b 4 WHERE b.name = a.name 5 GROUP BY b.name 6 HAVING COUNT(*) > 1 AND MIN(b.register_num) < a.register_num)
最小の register_num が重複している場合は、重複した分は抽出できませんが。
投稿2016/10/07 02:15
編集2016/10/07 02:19総合スコア295
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
SQL
1SELECT `id` 2FROM `tbl` WHERE `register_num` 3IN( 4 SELECT MAX(`register_num`) FROM `tbl` GROUP BY `name` HAVING COUNT(*) >= 2 5);
試してないので間違えてたらごめんなさい!
投稿2016/10/07 02:02
総合スコア830
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/10/07 02:20
2016/10/07 02:38
2016/10/07 06:41
0
ベストアンサー
これってよくあるランク付け処理でいけますよね
元データ
- register_numにダブりがある場合も想定する
- 複数存在しないnameデータも想定する
SQL
1create table tbl(id int not null primary key,name varchar(20),register_num int not null); 2insert into tbl values(1,'田中',1),(2,'大橋',53),(3,'吉田',21),(4,'田中',23),(5,'田中',4),(6,'吉田',2),(7,'大橋',324),(8,'大橋',56),(9,'吉田',2356),(10,'田中',1),(11,'吉田',21),(12,'鈴木',100);
ロジック
- nameごとにregister_num順にランク付けをします。
- register_numがダブった場合にid順でランク付けをします
SQL
1select t1.*,(select count(*)+1 from tbl as t2 where t1.name=t2.name and (t1.register_num>t2.register_num or (t1.register_num=t2.register_num and t1.id>t2.id))) as rank 2from tbl as t1 3order by name,rank,id
今回欲しいデータはあくまでもランクが2位以下のidだけなので
select id from tbl as t1 having (select count(*)+1 from tbl as t2 where t1.name=t2.name and (t1.register_num>t2.register_num or (t1.register_num=t2.register_num and t1.id>t2.id)))>1
投稿2016/10/07 03:23
総合スコア114843
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
0
こんな感じでは
sql
1select t1.* from `tbl` t1 2join (SELECT name, min(register_num) register_num FROM `tbl` GROUP BY name HAVING COUNT(*) > 1) t2 3on t1.name=t2.name and t1.register_num != t2.register_num;
DELETE
sql
1delete from `tbl` tt using 2(select t1.* from `tbl` t1 3join (SELECT name, min(register_num) register_num FROM `tbl` GROUP BY name HAVING COUNT(*) > 1) t2 4on t1.name=t2.name and t1.register_num != t2.register_num) t3 5where tt.id=t3.id;
投稿2016/10/07 02:34
編集2016/10/07 02:43総合スコア4070
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。