用sql语句删除重复记录

Examples

For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date:

SELECT department_id, last_name, employee_id, ROW_NUMBER()
   OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
   FROM employees;

DEPARTMENT_ID LAST_NAME                 EMPLOYEE_ID     EMP_ID
------------- ------------------------- ----------- ----------
           10 Whalen                            200          1
           20 Hartstein                         201          1
           20 Fay                               202          2
           30 Raphaely                          114          1
           30 Khoo                              115          2
           30 Baida                             116          3
           30 Tobias                            117          4
           30 Himuro                            118          5
           30 Colmenares                        119          6
           40 Mavris                            203          1
. . .
          100 Popp                              113          6
          110 Higgins                           205          1
          110 Gietz                             206          2

ROW_NUMBER is a nondeterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.

评论

3 楼 zhile005 2011-03-16  

高效的删除重复记录方法 (因为使用了ROWID)

DELETE FROM EMP E 
WHERE E.ROWID > (SELECT MIN(X.ROWID) 
FROM EMP X 
WHERE X.EMP_NO = E.EMP_NO);

2 楼 zhile005 2011-03-16  

高效的删除重复记录方法 (因为使用了ROWID)
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

1 楼 zhile005 2011-02-28  

Oracle Database Online Documentation 10g Release 2 (10.2)
file://H:\Oracle\B19306_01\index.htm


非常不错，值得学习！