前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >数据库去重有几种方法_去重数据库

数据库去重有几种方法_去重数据库

作者头像
全栈程序员站长
发布于 2022-11-10 10:37:36
3.8K0
发布于 2022-11-10 10:37:36
举报
文章被收录于专栏：全栈程序员必看全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

目录

概述

一、Oracle数据库去重（推荐放在在第6点）

二、MySQL数据库去重

三、sqlserver数据库去重

四、pg数据库删除重复数据

概述

转发这个主要是有时会有重复数据的需求，留一个查询方法，大家有空也可以测试一下..

一、Oracle数据库去重（推荐放在在第6点）

1、环境准备

可以看到“ALLEN”和“SMITH”这两个人的数据重复了，现在要求表中name重复的数据只保留一行，其他的删除。

代码语言：javascript
复制
CREATE TABLE hwb(
    id int,
    name varchar(10)
);

INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
commit;
SELECT * FROM hwb;
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

2、实现方法一：通过分组后，拿最小的id重复删除

代码语言：javascript
复制
 delete from hwb a where a.id  in      
   (select min(id) FROM hwb a group by name having count(name) > 1)
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

3、实现方法二：通过name相同，id不同的方式来判断

代码语言：javascript
复制
DELETE FROM hwb a
 WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.id > b.id);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

4、实现方法三：用rowid代替id，替换删除

代码语言：javascript
复制
DELETE FROM hwb a
 WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.rowid > b.rowid);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

5、实现方法四：通过分析函数根据name分组生成序号，然后删除序号大于1的数据。

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

代码语言：javascript
复制
DELETE FROM hwb a
WHERE ROWID IN (SELECT rid
                   FROM (SELECT ROWID as rid,
                                ROW_NUMBER() OVER(PARTITION BY name ORDER BY id) AS seq
                           FROM hwb)
                  WHERE seq > 1);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

6、【推荐】实现方法五：根据rowid删除

代码语言：javascript
复制
DELETE FROM hwb a
 WHERE a.ROWID > (SELECT MIN(b.ROWID) FROM hwb b WHERE a.name = b.name);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

二、MySQL数据库去重

1、环境准备

代码语言：javascript
复制
CREATE TABLE hwb(
    id BIGINT (4) not null,
    name varchar(10),
    PRIMARY KEY (id) 
) ENGINE = INNODB;

INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
commit;
SELECT * FROM hwb;

2、实现方法一：通过分组后，拿最大/最小的id重复删除

代码语言：javascript
复制
--重复删除多次
delete from hwb 
where id in (select id from 
       (select max(b.id) as id FROM hwb b group by name having count(name) > 1)c )

--或者修改为以下，只需删除一次
delete from hwb 
WHERE
NAME IN (select name from ( SELECT NAME FROM hwb b GROUP BY NAME HAVING count( NAME ) > 1 ) t)
AND id NOT IN (select tt.id from (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1 ) tt)
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

3、实现方法二：

根据name分组，查找出id最小的,然后再查找id不包含刚才查出来的。这样就查询出了所有的重复数据（除了id最小的那行）

代码语言：javascript
复制
delete from hwb 
WHERE
 id NOT IN (select tt.id from (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME ) tt)
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

4、实现方法三：通过name相同，id不同的方式来判断

代码语言：javascript
复制
delete  from hwb  where  id in ( 
select id from (
select * from hwb a
WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.id > b.id))c );
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

5、实现方法四：用rowid代替id，替换删除

在 Oracle 数据库的表中的每一行数据都有一个唯一的标识符，称为 rowid ，在 Oracle 内部通常就是使用它来访问数据的。

而在 MySQL 中也有一个类似的隐藏列 _rowid 来标记唯一的标识。但是需要注意 _rowid 并不是一个真实存在的列，其本质是一个 非空唯一列 的别名。

在某些情况下 _rowid 是不存在的，其只存在于以下情况：

1)当表中存在一个 数字类型 的单列主键时， _rowid 其实就是指的是这个主键列

2)当表中 不存在主键 但存在一个 数字类型 的 非空唯一列 时， _rowid 其实就是指的是对应 非空唯一列 。

需要注意以下情况是不存在 _rowid 的

1)主键列 或者 非空唯一列 的类型不是 数字类型

2)主键 是联合主键

3)唯一 列不是非空的。

代码语言：javascript
复制
delete  from hwb  where  id in ( 
select id from (
select * from hwb a
WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a._rowid > b._rowid))c );
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

ps:还可考虑表切换完成去重步骤。

三、sqlserver数据库去重

1、环境准备

代码语言：javascript
复制
CREATE TABLE [dbo].[hwb] (
  [id] decimal(12)   NULL,
  [name] nvarchar(10) COLLATE Chinese_PRC_CI_AS  NOT NULL
)
GO

ALTER TABLE [dbo].[hwb] SET (LOCK_ESCALATION = TABLE)
GO

INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
GO

2、实现方法一：通过分组后，拿最大/最小的id重复删除

代码语言：javascript
复制
--重复删除多次
delete from hwb 
 where id  in
       (select min(id) FROM hwb a group by name having count(name) > 1)

--或者修改为以下，只需删除一次
select * from hwb 
-- delete from hwb
WHERE
NAME IN (SELECT NAME FROM hwb b GROUP BY NAME HAVING count( NAME ) > 1 )
AND id NOT IN (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1 )
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

3、实现方法二：根据name分组，查找出id最小的,然后再查找id不包含刚才查出来的

这样就查询出了所有的重复数据（除了id最小的那行）

代码语言：javascript
复制
delete from hwb 
WHERE
 id NOT IN (select tt.id from (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME ) tt)
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

4、实现方法三：通过name相同，id不同的方式来判断

代码语言：javascript
复制
DELETE FROM hwb a
 WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.id > b.id);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

5、实现方法四：用hash值代替id，替换删除

%%lockres%%会返回聚集索引键的hash值，

非聚集索引，%%lockres%%会返回非聚集索引键的hash值

代码语言：javascript
复制
delete from hwb a
 WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.%%lockres%% > b.%%lockres%%);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

6、实现方法五：通过分析函数根据name分组生成序号，然后删除序号大于1的数据。

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)

在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。

代码语言：javascript
复制
DELETE FROM hwb a
WHERE
	id IN (
	SELECT	b.id 
	FROM	(SELECT id, ROW_NUMBER () OVER ( PARTITION BY name ORDER BY id ) AS seq FROM hwb) b 
	WHERE	b.seq > 1 );
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

7、【推荐】实现方法六：根据hash值删除

代码语言：javascript
复制
DELETE FROM hwb a
 WHERE a.%%lockres%% > (SELECT MIN(b.%%lockres%%) FROM hwb b WHERE a.name = b.name);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

四、pg数据库删除重复数据

1、环境准备

代码语言：javascript
复制
set search_path to 'public'; 
\dt   --list tables
CREATE TABLE hwb(
    id INT PRIMARY KEY  NOT NULL,
    name CHAR(10));

INSERT INTO hwb VALUES(1, 'TOM');
INSERT INTO hwb VALUES(2, 'ALLEN');
INSERT INTO hwb VALUES(3, 'ALLEN');
INSERT INTO hwb VALUES(4, 'SMITH');
INSERT INTO hwb VALUES(5, 'SMITH');
INSERT INTO hwb VALUES(6, 'SMITH');
SELECT * FROM hwb;

2、实现方法一：通过分组后，拿最大/最小的id重复删除

代码语言：javascript
复制
--重复删除多次
delete from hwb 
where id in (select max(b.id) as id FROM hwb b group by name having count(name) > 1)

--或者修改为以下，只需删除一次
delete  from hwb 
WHERE
NAME IN (SELECT NAME FROM hwb b GROUP BY NAME HAVING count(NAME) > 1)
AND id NOT IN (SELECT max(c.id ) as id FROM hwb c GROUP BY NAME HAVING count( NAME ) > 1);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

3、实现方法二：

根据name分组，查找出id最小的,然后再查找id不包含刚才查出来的。这样就查询出了所有的重复数据（除了id最小的那行）

代码语言：javascript
复制
delete  from hwb 
WHERE id NOT IN (SELECT min(c.id ) as id FROM hwb c GROUP BY NAME );
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

4、实现方法三：通过name相同，id不同的方式来判断

代码语言：javascript
复制
delete from hwb a
WHERE EXISTS (SELECT 1
          FROM hwb b
         WHERE a.name = b.name
           AND a.id > b.id);
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

5、[推荐]实现方法四：用ctid代替id，替换删除

代码语言：javascript
复制
delete from hwb a
WHERE EXISTS (SELECT 1 FROM hwb b
WHERE a.name = b.name AND a.ctid > b.ctid);

--或者如下：
select * from hwb WHERE ctid NOT IN (SELECT min(ctid) as id FROM hwb GROUP BY NAME );
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

6、[最高效推荐]实现方法五：用ctid代替id，结合row_number替换删除

代码语言：javascript
复制
delete FROM hwb a WHERE a.ctid = 
ANY (array (SELECT ctid FROM (SELECT row_number() over(PARTITION BY NAME) as seq, ctid FROM hwb ) t 	
WHERE t.seq > 1));
超详细的四类数据库去重实现方案汇总，值得收藏
超详细的四类数据库去重实现方案汇总，值得收藏

本文转载至头条用户“波波说运维”

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/187117.html原文链接：https://javaforall.cn

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2022年10月1日 ，如有侵权请联系 cloudcommunity@tencent.com 删除
oracle
数据库
sql
云数据库 SQL Server

本文分享自 作者个人站点/博客 前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

oracle
数据库
sql
云数据库 SQL Server
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • 概述
  • 一、Oracle数据库去重（推荐放在在第6点）
  • 二、MySQL数据库去重
  • 三、sqlserver数据库去重
  • 四、pg数据库删除重复数据
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
产品介绍产品文档
云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论