SQL指南書　重複業の削除（自己結合）

SQL 指南書集目次 - ray88’s diary
【お題】
以下のテーブルより重複行を削除する

【下準備】

--下準備：テーブルにシリアルナンバーの列を追加する
ALTER TABLE Products2 ADD COLUMN id SERIAL PRIMARY KEY;

【重複行削除のＳＱＬ①】
　各重複グループから最大のidを持つ行のみ残し、その他は削除する
　※WHERE id < (SELECT MAX(P2.id)...)の条件は、
　　P1のidがそのグループの最大idよりも小さい場合にTRUEとなる。
　　つまり、各重複グループの中で最大のidを持つ行以外の行が削除対象となります。

--重複行を削除するSQL　その１：極値関数「MAX」を利用
DELETE FROM Products2 P1
WHERE id < (SELECT MAX(P2.id)
            FROM Products2 P2
            WHERE P1.name = P2.name AND P1.price = P2.price);

【重複行削除のＳＱＬ②】
※EXISTSは、内側のサブクエリが1行以上の結果を返す場合にTRUEとなる。
　この場合、それはP1と同じnameとpriceを持ち、かつP2のidがP1のidより大きい行が存在することを
　意味する。これにより、各重複グループの中で最大のidを持つ行以外の行が削除対象となります。

--重複行を削除するSQL　その２：非等値結合 「<」,「>」 を利用
DELETE FROM Products2 P1
WHERE EXISTS (SELECT *
              FROM Products2 P2
              WHERE P1.name = P2.name
              AND P1.price = P2.price
              AND P1.id < P2.id);

【参考】テーブル作成用SQL
※シリアル番号なしのテーブル

CREATE TABLE Products2
(name VARCHAR(16),price INTEGER NOT NULL);

INSERT INTO Products2 VALUES('りんご',	50);
INSERT INTO Products2 VALUES('みかん',	100);
INSERT INTO Products2 VALUES('みかん',	100);
INSERT INTO Products2 VALUES('みかん',	100);
INSERT INTO Products2 VALUES('バナナ',	80);

※最初からシリアルナンバー付きのテーブルを作成する場合はこちら

CREATE TABLE Products2
(
    id SERIAL,
    name VARCHAR(16),
    price INTEGER NOT NULL
);

INSERT INTO Products2(name, price) VALUES('りんご', 50);
INSERT INTO Products2(name, price) VALUES('みかん', 100);
INSERT INTO Products2(name, price) VALUES('みかん', 100);
INSERT INTO Products2(name, price) VALUES('みかん', 100);
INSERT INTO Products2(name, price) VALUES('バナナ', 80);