oracle数据表删除重复数据

去重查询：

SQL> select * from flash_tbl; ID VL ---------- -- 10 I 11 J 12 K 13 L 14 M 15 N 16 O 17 P 18 Q 19 R 20 S ID VL ---------- -- 1 / 2 A 3 B 4 C 5 D 6 E 7 F 8 G 9 H 1 / 2 A ID VL ---------- -- 3 B 4 C 5 D 6 E 7 F 8 G 9 H 1 / 2 A 3 B 4 C ID VL ---------- -- 5 D 6 E 7 F 8 G 9 H 38 rows selected. SQL> select distinct id,vl from flash_tbl; ID VL ---------- -- 11 J 14 M 7 F 8 G 20 S 4 C 5 D 12 K 16 O 17 P 1 / ID VL ---------- -- 10 I 13 L 2 A 3 B 15 N 18 Q 6 E 9 H 19 R 20 rows selected.

删除重复数据（两种方式，下列id为主键）

SQL> select * from flash_tbl a where rowid !=(select max(rowid) from flash_tbl b where a.id=b.id);    -----查出重复数据 SQL> delete from flash_tbl a where rowid !=(select max(rowid) from flash_tbl b where a.id=b.id);      -----删掉重复数据 ----------------------------------------------------------------------------------------------------------------------------------- SQL> select * from flash_tbl where id in (select id from flash_tbl group by id having count(id) > 1);    -----查出重复数据 SQL> delete from flash_tbl where id in (select id from flash_tbl group by id having count(id) > 1) and rowid not in (select min(rowid) from flash_tbl group by Id having count(Id)>1);     -----删掉重复数据 ------------------------------------------------------------------------------------------------------------------------------------

补充：（表没主键时）

SQL> select * from flash_tbl  where rowid not in (select max(rowid) from flash_tbl group by id,vl);    -----查出重复数据 SQL> delete from flash_tbl  where rowid not in (select max(rowid) from flash_tbl group by id,vl); -----删掉重复数据