元の製品バージョン: SQL Server
元の KB 番号: 70956
まとめ
この記事では、Transact-SQL (T-SQL) スクリプトを使用して、Microsoft SQL Serverのテーブルから重複する行を削除する方法について説明します。 ここでは、重複する各行の 1 つのインスタンスを別のテーブルにコピーし、ROW_NUMBER 関数を使用してその場で余分な行を削除する 2 つの一般的な方法について説明します。 テーブルに一意の制約や主キーがないために繰り返しレコードが含まれている場合は、この記事を参照してください。 方法 2 は、インプレースで処理するため、より簡単です。
重複する行を含むサンプル テーブルを作成する
両方の方法を示すには、列 key_value を 1 つ持つ original_table という名前のサンプル テーブルを作成し、重複するデータを追加します。
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)
次に、次のいずれかの方法を使用して、テーブルから重複する行を削除します。
Note
これらのスクリプトは、データを完全に削除します。 それらを実稼働データに対して実行する前に、テーブルまたはデータベースをバックアップします。
方法 1: 重複する行を別のテーブルに移動し、1 つのコピーを再挿入する
方法 1 では、2 つのテーブル (original_table と、スクリプトが作成してから削除する 2 つ目のテーブル duplicate_table) を使用します。 スクリプトを別のテーブルに適用するには、original_table をテーブル名で置き換えます。 このメソッドでは、重複が複数の列によって定義されている場合は、 GROUP BY と COUNT ロジックを書き換える必要があります。
次のスクリプトを実行してください:
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: ROW_NUMBER関数を使用して重複する行を削除する
Microsoft SQL Server 2005 年に導入された ROW_NUMBER 関数を使用すると、この操作がはるかに簡単になります。
key_valueを、重複を定義する列に置き換えます。
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 式を使用してこれを行うことができます。
重複を削除するときに最新の行を保持する
最新の値を持つ行を保持するには、その列の降順で並べ替えます。 たとえば、同じ従業員に対して複数の行を持つ架空の employee_table を考えてみましょう。 次のスクリプトでは、最新の updated_date 値を持つ行のみが保持されます。
DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
PARTITION BY employee_name
ORDER BY updated_date DESC
)
FROM employee_table
) AS T
WHERE DupRank > 1
最新の updated_date 値を持つ行は 1 の DupRank を受け取るので、スクリプトはそれを保持し、古い行を削除します。
なぜ方法 2 が推奨されるのですか？
方法 2 は、次のような理由で簡単で効果的です。
- 重複するレコードを別のテーブルに一時的にコピーする必要はありません。
- 元のテーブルをそれ自体と結合する必要はありません (たとえば、
GROUP BYとHAVINGの組み合わせを使用してすべての重複レコードを返すサブクエリを使用する)。 - 最適なパフォーマンスを得るには、テーブルに、
key_valueをインデックス キーとして使用し、ORDER BY式で使用した可能性のある並べ替え列を含む対応するインデックスが必要です。
ROW_NUMBER関数は、SQL Server 2005 以降のバージョンで使用できます。
ROW_NUMBER関数をサポートしていないバージョンのSQL Serverを使用する場合は、代わりにメソッド 1 または同様のメソッドを使用します。
テーブル内の行の重複を防ぐにはどうすればよいですか?
重複を削除した後は、行を識別する列または列に 一意の制約 または 一意のインデックス を定義することで、新しい重複する行を防ぐことができます。