スクリプトを使用して SQL Server テーブルから重複する行を削除する

この記事では、Microsoft SQL Server のテーブルから重複する行を削除するために使用できるスクリプトについて説明します。

元の製品バージョン: SQL Server
元の KB 番号: 70956

概要

SQL Server テーブルから重複するレコードを削除するために使用できる一般的な方法は 2 つあります。 デモを行う場合は、まずサンプル テーブルとデータを作成します。

CREATE TABLE original_table (key_value int )

INSERT INTO original_table values (1)
INSERT INTO original_table values (1)
INSERT INTO original_table values (1)

INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)
INSERT INTO original_table values (2)

次に、次の方法を試して、テーブルから重複する行を削除します。

方法 1

次のスクリプトを実行します。

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

このスクリプトでは、次のアクションを指定された順序で実行します。

• 元のテーブル内の重複する行の 1 つのインスタンスを、重複するテーブルに移動します。
• 重複するテーブル内にある元のテーブルからすべての行を削除します。
• 重複するテーブル内の行を元のテーブルに戻します。
• 重複するテーブルを削除します。

この方法は簡単です。 ただし、重複するテーブルを一時的にビルドするには、データベースで十分な領域が必要です。 この方法は、データを移動するため、オーバーヘッドも発生します。

また、テーブルに IDENTITY 列がある場合は、元のテーブルにデータを復元するときに SET IDENTITY_INSERT ON を使用する必要があります。

方法 2

Microsoft SQL Server 2005 で導入されたROW_NUMBER関数を使用すると、この操作がはるかに簡単になります。

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1 

このスクリプトでは、次のアクションを指定された順序で実行します。

• ROW_NUMBER 関数を使用して、コンマで区切られた 1 つ以上の列である key_value に基づいてデータを分割します。
• 1 より大きい DupRank 値を受け取ったすべてのレコードを削除します。 この値は、レコードが重複していることを示します。

(SELECT NULL)式のため、スクリプトは条件に基づいてパーティション分割されたデータを並べ替えません。 重複を削除するロジックで、削除するレコードと、他の列の並べ替え順序に基づいて保持するレコードを選択する必要がある場合は、式を ORDER BY 使用してこれを行うことができます。

詳細

方法 2 は、次の理由で簡単で効果的です。

• 重複するレコードを別のテーブルに一時的にコピーする必要はありません。
• 元のテーブルをそれ自体と結合する必要はありません (たとえば、 と HAVINGの組み合わせをGROUP BY使用して、すべての重複レコードを返すサブクエリを使用します)。
• 最適なパフォーマンスを得るには、 をインデックス キーとして使用し、式で使用 key_value した可能性がある並べ替え列を含む対応するインデックスをテーブルに用意する ORDER BY 必要があります。

ただし、このメソッドは、ROW_NUMBER関数をサポートしていない古いバージョンのSQL Serverでは機能しません。 このような場合は、 代わりにメソッド 1 または同様のメソッドを使用する必要があります。