Oracle 使用分析函式刪除表中的重複行

feelpurple發表於2016-02-17
Oracle函式
在 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部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-1989906/，如需轉載，請註明出處，否則將追究法律責任。

相關文章