やや複雑な抽出SQL（MySQL）を組みたいのですが、中々上手く行きません

Question

MySQLに関して、質問させて頂きます。

画像のようなテーブルがあります。

nameが同じレコードが多数入っており、１つを残して全て削除したいと考えています。
ただ、そのままDELETEはできず、一度どれだけの重複があるのか調査をしなければなりません。

その上で、下記を実現するSQLを組もうとしているのですが、中々上手く行きません。

●nameが同じレコードが２つ以上存在する物で、register_numが最も小さいものでは無いレコードのidを抽出したい

SELECT `id` FROM tbl GROUP BY name HAVING COUNT(*) >= 2

で抽出しましたが、これですとregister_numが**最も小さいもの__以外__**のレコードid全てを抽出する事ができません。

どなたかご教示頂けますと幸いです。

Comments

[Orlofsky]

SQLの質問はCREATE TABLE や INSERT でこんなデータがある、って明示した方が解決が早いです。

Answer 1

PostgreSQL9.5で作ってみました :-)

with tbl(name,register_num) as(
values('大橋',  53),
      ('大橋',  56),
      ('大橋', 324),
      ('田中',   1),
      ('田中',   4),
      ('田中',  23),
      ('吉田',   2),
      ('吉田',  21),
      ('吉田',2356))
select *
  from tbl a
 where exists(select 1 from tbl b
               where b.name = a.name
                 and b.register_num < a.register_num);

 name | register_num
------+--------------
 大橋 |           56
 大橋 |          324
 田中 |            4
 田中 |           23
 吉田 |           21
 吉田 |         2356

Answer 2

SELECT `id` 
FROM `tbl` WHERE register_num <> 
(SELECT MIN(register_num) FROM tbl AS t2 WHERE tbl.name = t2.name)

Comments

[Razumerians]

参考にさせていただきます。ご丁寧にありがとうございました。

Answer 3

SELECT DISTINCT t1.* 
FROM test AS t1 
INNER JOIN test AS t2 ON t1.name = t2.name
  AND t1.register_num > t2.register_num
;

ただし、上のSQLでは
「nameが同じでregister_numが最も小さい行」
が複数ある場合、そのような行は全て抽出できません。

もし、そのような行もただ1行だけを残して削除したい場合、
以下のようにSQLを修正すると、idが最小でない方を抽出できます。

SELECT DISTINCT t1.* 
FROM test AS t1 
INNER JOIN test AS t2 ON t1.name = t2.name
  AND (t1.register_num > t2.register_num
  OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 
;

抽出した行を削除する場合は、SELECT DISTINCT t1.*をDELETE t1に変更してください。

---

mysql> CREATE TABLE test (
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    ->   name varchar(8),
    ->   register_num int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test (name, register_num) VALUES
    ->   ('田中', 1),
    ->   ('大橋', 53),
    ->   ('吉田', 21),
    ->   ('田中', 23),
    ->   ('田中', 4),
    ->   ('吉田', 2),
    ->   ('大橋', 324),
    ->   ('大橋', 56),
    ->   ('吉田', 2356),
    ->   ('吉田', 2);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT t1.* 
    -> FROM test AS t1 
    -> INNER JOIN test AS t2 ON t1.name = t2.name
    ->   AND t1.register_num > t2.register_num
    -> ;
+----+--------+--------------+
| id | name   | register_num |
+----+--------+--------------+
|  4 | 田中   |           23 |
|  5 | 田中   |            4 |
|  7 | 大橋   |          324 |
|  8 | 大橋   |           56 |
|  9 | 吉田   |         2356 |
|  3 | 吉田   |           21 |
+----+--------+--------------+
6 rows in set (0.00 sec)

mysql> SELECT DISTINCT t1.* 
    -> FROM test AS t1 
    -> INNER JOIN test AS t2 ON t1.name = t2.name
    ->   AND (t1.register_num > t2.register_num
    ->   OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 
    -> ;
+----+--------+--------------+
| id | name   | register_num |
+----+--------+--------------+
|  4 | 田中   |           23 |
|  5 | 田中   |            4 |
|  7 | 大橋   |          324 |
|  8 | 大橋   |           56 |
|  9 | 吉田   |         2356 |
|  3 | 吉田   |           21 |
| 10 | 吉田   |            2 |
+----+--------+--------------+
7 rows in set (0.00 sec)

mysql> DELETE t1  
    -> FROM test AS t1 
    -> INNER JOIN test AS t2 ON t1.name = t2.name
    ->   AND t1.register_num > t2.register_num
    -> ;
Query OK, 6 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+----+--------+--------------+
| id | name   | register_num |
+----+--------+--------------+
|  1 | 田中   |            1 |
|  2 | 大橋   |           53 |
|  6 | 吉田   |            2 |
| 10 | 吉田   |            2 |
+----+--------+--------------+
4 rows in set (0.00 sec)

mysql> DELETE t1  
    -> FROM test AS t1 
    -> INNER JOIN test AS t2 ON t1.name = t2.name
    ->   AND (t1.register_num > t2.register_num
    ->   OR (t1.register_num = t2.register_num AND t1.id > t2.id)) 
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test;
+----+--------+--------------+
| id | name   | register_num |
+----+--------+--------------+
|  1 | 田中   |            1 |
|  2 | 大橋   |           53 |
|  6 | 吉田   |            2 |
+----+--------+--------------+
3 rows in set (0.00 sec)

Comments

[Razumerians]

参考にさせていただきます。ご丁寧にありがとうございました。

Answer 4

以下でどうでしょう。

Select id FROM tbl a
WHERE 
Exists (SELECT * FROM tbl b 
    WHERE b.name = a.name
    GROUP BY b.name 
    HAVING COUNT(*) > 1 AND MIN(b.register_num) < a.register_num)

最小の register_num が重複している場合は、重複した分は抽出できませんが。

Comments

[Razumerians]

参考にさせていただきます。ご丁寧にありがとうございました。

Answer 5

SELECT `id` 
FROM `tbl` WHERE `register_num` 
IN(
    SELECT MAX(`register_num`) FROM `tbl` GROUP BY `name` HAVING COUNT(*) >= 2
);

試してないので間違えてたらごめんなさい！

Comments

[maisumakun]

これだと、同じnameが3行以上あっても出力は1行になってしまいますね。

[YashaWedyue]

そうですね、質問修正を見て勘違いに気付きました・・・

[Razumerians]

参考にさせていただきます。ご丁寧にありがとうございました。

Answer 6

これってよくあるランク付け処理でいけますよね

元データ

• register_numにダブりがある場合も想定する
• 複数存在しないnameデータも想定する

create table tbl(id int not null primary key,name varchar(20),register_num int not null);
insert 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順でランク付けをします

select 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
from tbl as t1
order 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

Comments

[Razumerians]

ありがとうございます！
こちらで解決しました。大変ご丁寧に回答頂き、ありがとうございました。

Answer 7

こんな感じでは

select t1.* from `tbl` t1
join (SELECT name, min(register_num) register_num FROM `tbl` GROUP BY name HAVING COUNT(*) > 1) t2
on t1.name=t2.name and t1.register_num != t2.register_num;

DELETE

delete from `tbl` tt using
(select t1.* from `tbl` t1
join (SELECT name, min(register_num) register_num FROM `tbl` GROUP BY name HAVING COUNT(*) > 1) t2
on t1.name=t2.name and t1.register_num != t2.register_num) t3
where tt.id=t3.id;

Comments

[Razumerians]

参考にさせていただきます。ご丁寧にありがとうございました。