删除数据表重复记录，并保留最小rowid

• 例如有一张表city，表内容如下

create table CITY
(
	cityno   NUMBER(2),
	cityname VARCHAR2(14)
)

CITYNO	CITYNAME
1	北京
2	天津
3	石家庄
4	郑州
5	武汉
6	长沙
7	石家庄
8	北京

• 查询包含 rowid 结果如下：

SELECT a.*, a.rowid FROM city a

CITYNO	CITYNAME	ROWID
1	北京	AAAM1CAAEAAAAGcAAA
2	天津	AAAM1CAAEAAAAGeAAA
3	石家庄	AAAM1CAAEAAAAGeAAB
4	郑州	AAAM1CAAEAAAAGeAAC
5	武汉	AAAM1CAAEAAAAGeAAD
6	长沙	AAAM1CAAEAAAAGeAAE
7	石家庄	AAAM1CAAEAAAAGeAAF
8	北京	AAAM1CAAEAAAAGeAAG

• 查询需要删除的重复的记录，并保留最小的rowid

SELECT a.*, a.rowid
  FROM city a
 WHERE a.rowid IN (SELECT max(rowid)
                     FROM city t2
                    WHERE 1 = 1
                    GROUP BY t2.cityname
                   HAVING COUNT(cityname) > 1)

CITYNO	CITYNAME	ROWID
7	石家庄	AAAM1CAAEAAAAGeAAF
8	北京	AAAM1CAAEAAAAGeAAG

• 删除

DELETE
  FROM city a
 WHERE a.rowid IN (SELECT max(rowid)
                     FROM city t2
                    WHERE 1 = 1
                    GROUP BY t2.cityname
                   HAVING COUNT(cityname) > 1)