【SQL】重複した行を1つだけ残して、2つ目以降をDELETEしたい

Question

SQL文についての質問です。
同じ値を持つレコードの2件目以降をDELETEしたいのですが、どのようなSQL文にしたらよろしいでしょうか？

環境：MySQL　バージョンMySQL5.7
XserverでMySQLを使っています。
XserverのMySQLのデータベースにWorkBenchを使って接続して、WorkBenchで操作しています。

条件：
列Bは重複しない
列Bが重複している場合は、列Cは新しい日を残す。
列Bが重複している場合で、列Cに同日が存在する場合は1行だけ残し、削除する。

「元データ」の状態から「DELETE後の状態」ののようにしたいです。

元データ
|列A|列B|列C|
|1|りんご|2021-09-01|
|1|りんご|2021-09-01|
|1|りんご|2021-09-02|
|1|みかん|2021-09-02|
|1|レモン|2021-09-01|
|1|レモン|2021-09-02|
|1|メロン|2021-09-02|
|1|スイカ|2021-09-02|

DELETE後の状態
|列A|列B|列C|
|1|りんご|2021-09-02|
|1|みかん|2021-09-02|
|1|レモン|2021-09-02|
|1|メロン|2021-09-02|
|1|スイカ|2021-09-02|

ご教授頂けますと幸いです。
よろしくお願いいたします。

Comments

[neko_the_shadow]

MySQLのバージョンはわかりますか?
またテーブルのPrimary Keyはどのように設定されていますか?

[syoco0330]

コメントありがとうございます。
XserverのMySQLを使っており、
バージョンはMySQL5.7になります。
別テーブルとの連携はしておらず、
PrimaryKeyやNotNullは設定しておりません。
WorkBenchを使って操作しています。

Answer 1

質問文の条件なら、最終的に欲しいデータはGROUP BYだけで取れるはずなんで、単純に洗い替えるのが一番わかりやすい。

CREATE TEMPORARY TABLE tmp (列A integer, 列B varchar(100), 列C date);
INSERT INTO tmp (列A, 列B, 列C) SELECT 列A, 列B, MAX(列C) FROM テーブル名 GROUP BY 列A, 列B;
TRUNCATE TABLE テーブル名;
INSERT INTO テーブル名 (列A, 列B, 列C) SELECT 列A, 列B, 列C FROM tmp;

（テーブル名とか型は適当）

Comments

[syoco0330]

コメントありがとうございます。
洗い替えるという発想に至りませんでした。シンプルで分かりやすい処理ですね。
とても勉強になります。こちらの方法でやってみようと思います。
ありがとうございます。

[gentaro]

本質的な話をすると、これは結局列Bごとに１行しか必要ない、という要件に見えるので、それであれば最初から質問文の「元データ」の状態にならないようにしておくべきでじゃないか、という疑問は残りますが。
例えば列BにUNIQUE制約を付けるなどして、更新時は既に存在するものについてはINSERTではなくUPDATEを行う、という事まで考えるべきじゃないかなと。（マスタ系のテーブルなのかトランザクション系のテーブルなのかもわからないから指摘しづらいけど）

[syoco0330]

＞それであれば最初から質問文の「元データ」の状態にならないようにしておくべきでじゃないか、という疑問は残りますが。
仰る通りですね。ローカル環境にあるPythonからXserverのデータベースに直接INSERTできなかったため、データをCSVにしてWorkBenchに入れますが、INSERTとUPDATEが上手くいかなかったため、UNIQUE制約もうまくいかずにまわりくどいやり方になってしまっています。
もう少しやり方を検討する必要がありますが、SQL文については今回の件で大変勉強になりました。

Answer 2

CREATE TEMPORARY TABLE tmp (列A integer, 列B varchar(100), 列C date);
INSERT INTO tmp (列A, 列B, 列C) SELECT 列A, 列B, MAX(列C) FROM テーブル名 GROUP BY 列A, 列B;
TRUNCATE TABLE テーブル名;
INSERT INTO テーブル名 (列A, 列B, 列C) SELECT 列A, 列B, 列C FROM tmp;

で解決

Answer 3

create table tbl (a int,b varchar(10),c date);
insert into tbl values
(1,'りんご','2021-09-01'),
(1,'りんご','2021-09-01'),
(1,'りんご','2021-09-02'),
(1,'みかん','2021-09-02'),
(1,'レモン','2021-09-01'),
(1,'レモン','2021-09-02'),
(1,'メロン','2021-09-02'),
(1,'スイカ','2021-09-02');


select distinct * from tbl as t1
where not exists(select 1 from tbl where b=t1.b and c>t1.c)

Comments

[yambejp]

あ、テーブルからデリートするんでしたね、失礼しました

[syoco0330]

コメントありがとうございます。
はい、テーブルからDELETEをしたいです。

[yambejp]

残念ながら運用方法が最悪です
完全に同一になるレコードは指定して削除することが面倒です
まず重複行を削除して、最大行を残す処理を冗長にやっていく
必要があるでしょう

[syoco0330]

ありがとうございます。
完全に同一になるレコードを削除することが意外と大変ということが理解できました。

Answer 4

やり方はいろいろあるけれど、LEFT JOIN で自己結合の下記がパフォーマンス的にもよくて一番簡単かな

SELECT
TBLX.A, TBLX.B, TBLX.C
FROM
TBL AS TBLX
LEFT JOIN TBL AS TBLY
ON (TBLX.B = TBLY.B AND TBLX.C < TBLY.C)
WHERE
TBLY.C IS NULL
AND TBLX.C IS NOT NULL

（一番最後の「AND TBLX.C IS NOT NULL」はC列に日付の値があるものだけを対象にするもので
NULLが入らないなら不要）

他にも
WHERE NOT EXISTS
とか
FROM 句で GROUP BY して最大値用のテーブルを用意
とかの方法がある

Comments

[syoco0330]

コメントありがとうございます。
自己結合という処理があるんですね。とても勉強になります。試してみます。
内容を理解するのに少し時間がかかりそうでしたので、まずはお返事にて失礼いたします。