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句を使うみたいなレベルで止まってる。