查询删除的数据 oracle,Oracle笔记——快速删除查询重复的数据

快速删除查询重复的数据

利用物理id的唯一性

---- 找出 相同数据 不可能有名字相同，岗位相同的人在同一个部门

select a.* from EMPLOYEES a

-- 找出重复的数据

where a.rowid !=(

select max(rowid) from EMPLOYEES

where

a.DEPARTMENT_ID = DEPARTMENT_ID

and a.EMPLOYEE_NAME = EMPLOYEE_NAME

and a.EMPLOYEE_JOB = EMPLOYEE_JOB

);

----- 删除重复的数据 --- 保留的是 最新添加进来的数据

select EMPLOYEE_ID,EMPLOYEE_NAME,EMPLOYEE_JOB,

MANAGER,SALARY,DEPARTMENT_ID from J10623.EMPLOYEES where EMPLOYEE_NAME= 'JAMES'

delete from EMPLOYEES a

where a.rowid !=(

select max(rowid) from EMPLOYEES

where

---- 查找重复数据的 条件

a.DEPARTMENT_ID = DEPARTMENT_ID

and a.EMPLOYEE_NAME = EMPLOYEE_NAME

and a.EMPLOYEE_JOB = EMPLOYEE_JOB

);