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

今天药忘吃喽~ 2024-04-17 23:51 28阅读 0赞

**在 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1989906/