質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.81%

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

解決済

回答 7

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,719

Razumerians

score 25

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

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

イメージ説明

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

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

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

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

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

アウトプット

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2016/10/07 11:37

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

    キャンセル

回答 7

+13

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+2

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 15:40

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

    キャンセル

+1

以下でどうでしょう。

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 が重複している場合は、重複した分は抽出できませんが。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 15:40

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

    キャンセル

+1

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)

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 15:40

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

    キャンセル

checkベストアンサー

0

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

元データ

  • 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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 15:40

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

    キャンセル

0

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


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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 11:20

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

    キャンセル

  • 2016/10/07 11:38

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

    キャンセル

  • 2016/10/07 15:41

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

    キャンセル

0

こんな感じでは

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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/10/07 15:40

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.81%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る