在 hr 的 employee 表中插入重复数据，first_name 和 last_name 重复
insert into hr.employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id) values
(210,'Janette','King','JKING2','650.555.8880',to_date('2009-03-25','yyyy-mm-dd'),'SA_REP',3500,0.25,145,80);

insert into hr.employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id) values
(211,'Patrick','Sully','PSULLY2','650.555.8881',to_date('2009-03-25','yyyy-mm-dd'),'SA_REP',3500,0.25,145,80);

insert into hr.employees (employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id) values
(212,'Allan','McEwen','AMCEWEN2','650.555.8882',to_date('2009-03-25','yyyy-mm-dd'),'SA_REP',3500,0.25,145,80);

查询重复数据
SQL> select employee_id, first_name, last_name
2 from hr.employees
3 where first_name in ('Janette', 'Patrick', 'Allan')
4 and last_name in ('King', 'Sully', 'McEwen')
5 order by first_name, last_name;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
158 Allan McEwen
212 Allan McEwen
210 Janette King
156 Janette King
211 Patrick Sully
157 Patrick Sully

已选择6行。

删除重复数据
delete from hr.employees
where rowid in (select rowid
from (select first_name,
last_name,
rowid,
row_number() over(partition by first_name, last_name order by employee_id) staff_row
from hr.employees)
where staff_row > 1);

ITPub博客

Oracle 使用分析函数删除表中的重复行

最新文章