ROWID面试题-删除表中重复数据(重复数据保留一个)

/*
ROWID是行ID,通过它一定可以定位到r任意一行的数据记录 ROWID DNAME DEPTNO LOC
------------------ ---------------------------- ---------- ----------
AAASSUAAEAAAAIbAAA ACCOUNTING 10 NEW YORK
AAASSUAAEAAAAIbAAB RESEARCH 20 DALLAS
AAASSUAAEAAAAIbAAC SALES 30 CHICAGO
AAASSUAAEAAAAIbAAD OPERATIONS 40 BOSTON
AAASSUAAEAAAAIeAAB RESEARCH 20 DALLAS
AAASSUAAEAAAAIeAAC SALES 30 CHICAGO 重复数据只是数据内容是重复的,而ROWID是唯一的 */ /*
删除表中重复数据: 分析:1.首先应该查询出所有需要被保留的数据(被保留的数据一定是最早增加的)
2.再根据(重复数据列)分组查询出各自分组中最小的rowid,只要分组查询的rowid不是在min(rowid)的集合中就全部删除 SQL> select deptno,dname,loc ,min(rowid) from mydept group by deptno,dname,loc; DEPTNO DNAME LOC MIN(ROWID)
---------- ---------------------------- -------------------------- --------------
20 RESEARCH DALLAS AAASSUAAEAAAAIbAAB
40 OPERATIONS BOSTON AAASSUAAEAAAAIbAAD
10 ACCOUNTING NEW YORK AAASSUAAEAAAAIbAAA
30 SALES CHICAGO AAASSUAAEAAAAIbAAC SQL> delete from mydept where rowid not in(select min(rowid) from mydept group by deptno, dname,loc); 已删除2行。 删除了重复数据的表数据如下: SQL> select * from mydept; DEPTNO DNAME LOC
---------- ---------------------------- ------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON */
上一篇:【POJ2104】【整体二分+树状数组】区间第k大


下一篇:windows下 破解 Sublime Text3 和汉化