频道栏目
首页 > 数据库 > 其他综合 > 正文
Delete Duplicatere cords
2015-09-02 09:54:48         来源:¥ = C^2 + C++  
收藏   我要投稿

查询目标

DeleteDuplicate Emails

Writea SQL query to delete all duplicate email entries in a table named Person,keeping only unique emails based on its smallest Id.

题目来自 Leetcode

Person表如下:

SQL> select * fromperson;

ID EMAIL
------------------------------------------------------------
1 jo@ex.com
2 jom@ex.com
3 jo@ex.com

 

建表及插入测试数据

droptable person purge;

createtable person(id int,email char(50));

insertinto person(id,email) values(1,'jo@ex.com');

insertinto person(id,email) values(2,'jom@ex.com');

insertinto person(id,email) values(3,'jo@ex.com');

 

得到的Person表中共3行数据。根据题目要求,第三行数据应该被删除。

SQL语句如下(oracle中执行):

方法一:使用MIN

SQL>delete from person a where a.id > (

select min(b.id) from person b where a.email=b.email

);

方法二:或者用 ANY

SQL>delete from person a where a.id > any (

select b.id from person bwhere a.email=b.email

);

方法三:使用GROUP BY & ROWID

SQL>deletefrom person where rowid not in(select min(rowid) from person group by email);

方法四:self-join

SQL>delete from person p1 where rowid not in (select min(rowid)from person p2 where p1.email=p2.email);

方法五:使用 ROW_NUMBER()

SQL > delete from person where rowid in (select rid from(select rowid rid , row_number() over(partition by email order by email)row_num from person) where row_num>1);

方法六:使用DENSE_RANK()

delete from person where rowid in (select rid from (select rowidrid , dense_rank() over(partition by email order by rowid) rank fromperson) where rank>1);

 

参考文章:

https://www.dba-oracle.com/t_delete_duplicate_table_rows.htm

https://sqlandplsql.com/2013/01/29/5-ways-to-delete-duplicate-records-oracle/

主要内容如下:

In Oracle there are many ways to delete duplicate records. Notethat below example are described to just explain the different possibilities.

Considerthe EMP table with below rows

createtable emp(
EMPNNO integer,
EMPNAME varchar2(20),
SALARY number);

10 Bill 2000
11 Bill 2000
12 Mark 3000
12 Mark 3000
12 Mark 3000
13 Tom 4000
14 Tom 5000
15 Susan 5000

1. Usingrowid

SQL >delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

Thistechnique can be applied to almost scenarios. Group by operation should be onthe columns which identify the duplicates.

2. Usingself-join

SQL> delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );

3. Using row_number()

SQL> delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);

This isanother efficient way to delete duplicates

4. Usingdense_rank()

SQL> delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid) rn
from emp
)
where rn > 1
);

Here youcan use both rank() and dens_rank() since both will give unique records whenorder by rowid.

5. Using groupby

Considerthe EMP table with below rows

10 Bill 2000
11 Bill 2000
12 Mark 3000
13 Mark 3000

SQL> delete from emp where
(empno,empname,salary) in
(
select max(empno),empname,salary from emp
group by empname,salary
);

 

点击复制链接 与好友分享!回本站首页
相关TAG标签
上一篇:使用操作系统命令来trace监听
下一篇:DB查询分析器6.04在Windows10上的安装与运行展示
相关文章
图文推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站