SQLServer 重複データを取り除く
概要
アプリケーションからExcel形式のデータを取り込んでデータベースに登録してほしい。
よくある仕様かと思います。これに加えて、取り込みデータに重複があった場合、より後のレコードを有効としたいケースについてのソリューションです。
データ取り込み(イメージ)
取り込みデータが重複しています。
Name項目、Mail項目、Age項目があって、NameとMailでユニークであるとしたとき、以下の場合有効なAliceちゃんは17歳です。
DECLARE @inputData as myType INSERT INTO @inputData VALUES (N'Alice',N'Alice@simple.com',16) --無効 ,(N'Bob',N'Bob@simple.com',21) ,(N'Alice',N'Alice@simple.com',18) --無効 ,(N'Stacy',N'Stacy@simple.com',23) ,(N'Alice',N'Alice@simple.com',17) --有効☆ SELECT * FROM @inputData
SELECT結果
ID | Name | Age | |
---|---|---|---|
1 | Alice | Alice@simple.com | 16 |
2 | Bob | Bob@simple.com | 21 |
3 | Alice | Alice@simple.com | 18 |
4 | Stacy | Stacy@simple.com | 23 |
5 | Alice | Alice@simple.com | 17 |
ユーザー定義テーブル型を作成
定義にIDENTITYを含めておきます。こうしておけば、Excelを取り込んだ際に自動採番してくれます。
DROP TYPE [dbo].[myType] GO CREATE TYPE [dbo].[myType] AS TABLE( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NOT NULL, [Mail] [nvarchar](50) NOT NULL, [Age] [int] NOT NULL ) GO
重複を取り除く
取り込みデータよりキー項目でグルーピングして、最大値より小さいID値を削除します。
もしも最初に出てきたレコードを有効にしたい場合は、逆に最小値より大きいID値を削除すればOKですね。
DELETE core FROM @inputData core WHERE core.ID < (SELECT Max(t.ID) FROM @inputData t WHERE t.Name = core.Name AND t.Mail = core.Mail GROUP BY t.Name,t.Mail ) SELECT * FROM @inputData
SELECT結果
ID | Name | Age | |
---|---|---|---|
2 | Bob | Bob@simple.com | 21 |
4 | Stacy | Stacy@simple.com | 23 |
5 | Alice | Alice@simple.com | 17 |
取り込んだ順に連番が振られていれば、こいつを利用して有効なレコードのみに絞ることができます!
ユーザー定義テーブル型にIDENTITY列が定義されていなかった場合
私の場合がコレで、定義の変更も影響が大きいためやりたくなかった。
ROW_NUMBERも考えたんですが、OVER句のORDER BY句もデータ取り込み順のため使えない。
ので、前段としてIDENTITY列を追加しつつ一時テーブルを作成してみました。
こんなことができるんですね。
IF object_id('tempdb..#tmpData') IS NOT NULL BEGIN DROP TABLE #tmpData; END SELECT IDENTITY(int) AS ID, Name,Mail,Age INTO #tmpData FROM @inputData SELECT * FROM #tmpData
この一時テーブルを元に上記の重複を取り除く処理を行うことでユーザー定義テーブル型にIDENTITY列が定義されていなくてもID値を使うことができます。
sh-yoshida.hatenablog.com
sh-yoshida.hatenablog.com
sh-yoshida.hatenablog.com