主キーがないテーブルで、重複レコードの全部ではなく、一件残して重複レコードを削除したい。

SQLで重複行を削除する方法 : SQL Server - 蒼の王座・裏口に答えがあった。答え2つ目。

create table #TMP_TABLE(
	 COLUMN_A    int NULL
	,COLUMN_B    int NULL
	,COLUMN_C    int NULL
	,COLUMN_D    int NULL
);

insert into #TMP_TABLE values(1,  1,  1,  1);
insert into #TMP_TABLE values(2,  2,  2,  2);
insert into #TMP_TABLE values(3,  3,  3,  3);
insert into #TMP_TABLE values(4,  4,  4,  4);
insert into #TMP_TABLE values(5,  5,  5,  5);
insert into #TMP_TABLE values(6,  6,  6,  6);
insert into #TMP_TABLE values(7,  7,  7,  7);
insert into #TMP_TABLE values(8,  8,  8,  8);
insert into #TMP_TABLE values(9,  9,  9,  9);
insert into #TMP_TABLE values(10, 10, 10, 10);
insert into #TMP_TABLE values(1, 1, 1, 1);
insert into #TMP_TABLE values(1, 1, 1, 1);

select * from #TMP_TABLE order by COLUMN_A;

with CTE as ( 
  select
	row_number() over ( 
	  partition by
		COLUMN_A
		, COLUMN_B
		, COLUMN_C
		, COLUMN_D
	  order by
		COLUMN_A
	) RN 
  from
	#TMP_TABLE
) 
delete 
from
  CTE 
where
  RN > 1


select * from #TMP_TABLE order by COLUMN_A;

削除前

COLUMN_A	COLUMN_B	COLUMN_C	COLUMN_D
1	1	1	1
1	1	1	1
1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6
7	7	7	7
8	8	8	8
9	9	9	9
10	10	10	10

削除後

COLUMN_A	COLUMN_B	COLUMN_C	COLUMN_D
1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6
7	7	7	7
8	8	8	8
9	9	9	9
10	10	10	10

主キーがあって、それ以外の複数カラムで重複している場合などは、グルーピング、もしくはROW_NUMBERといった分析関数を使い、特定の値(最小値とかOracleであればROWIDとか)以外を削除すれば良いというのは、考えれば出てくるのだけど、主キー、ユニークキーもない場合、一行だけ残して削除する場合、どうするのか途方に暮れた。

一旦重複レコードはすべて削除して1件入れ直すとか考えたが、なるほど、WITH句共通テーブル式(CTE)を使って削除するというのは全く思いも付かなかった。VIEWを作って、それをデータ操作するという感覚なのかな。

再帰的クエリなどもそうだけど、WITH句共通テーブル式(CTE)の使い方が本当に理解できると難しいデータ取得とかも、SQLでドカンとやれるんだろうな。今の所、自分の中では、同じサブクエリを書かなきゃいけないようなときにWITH句を使うみたいなレベルで止まってる。

有馬総一郎のブログ

(彼氏の事情)

主キーがないテーブルで、重複レコードの全部ではなく、一件残して重複レコードを削除したい。

削除前

削除後