SQL删除重复的记录(只保留一条)-窗口函数row_number()

这篇具有很好参考价值的文章主要介绍了SQL删除重复的记录(只保留一条)-窗口函数row_number()。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、关于mysql表中数据重复

关于删除mysql表中重复数据问题,本文中给到两种办法:聚合函数、窗口函数row_number()的方法。
(注意:MySQL从8.0开始支持窗口函数)

测试数据准备:首先创建一个测试表test,插入一些测试数据,模拟一些重复数据(最终目标:删除重复数据,但不处理null行)
SQL删除重复的记录(只保留一条)-窗口函数row_number()
先查询下重复数据,确认待处理数据的数量,然后开始处理:

SELECT
        seq_id,
        out_user_code,
        COUNT( out_user_code ) count
FROM
        test
WHERE
        is_deleted = 0
        AND out_user_code IS NOT NULL
GROUP BY
        out_user_code
HAVING
        count( out_user_code )> 1

SQL删除重复的记录(只保留一条)-窗口函数row_number()

二、聚合函数min(id)+not in

思路:首先通过子查询取出 id 最小的不重复行,然后通过 not in 删除重复数据

1、首先查询一下 id 最小的不重复行(我们留下最早插入的数据,后面的重复数据都删除):

SELECT
    min(seq_id) seq_id,
	out_user_code,
	COUNT( out_user_code ) count 
FROM
	test 
GROUP BY
	out_user_code 

SQL删除重复的记录(只保留一条)-窗口函数row_number()

2、通过查询结果可知,重复的数据行seq_id为2、7的数据过滤掉了,接下来NOT IN 操作应该删除2、7重复数据行。那按照假设想法执行NOT IN:

DELETE from test where r.seq_id not in (
	SELECT
	min(t.seq_id) seq_id
	FROM
		test  t
	GROUP BY
		t.out_user_code
)  r

会发现报错:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r' at line 8, Time: 0.007000s

原因:不能先select出同一表中的某些值,再update这个表(在同一语句中),即不能依据某字段值做判断再来更新某字段的值。

解决方案:可将SELECT出的结果再通过中间表SELECT一遍。

3、最终处理sql:

DELETE from test where seq_id not in (
	SELECT r.seq_id from (
		SELECT
		   min(t.seq_id) seq_id
		FROM
			test  t
		GROUP BY
			t.out_user_code
	)  r 
) and out_user_code is not null

换种写法(保证相关字段有索引):

DELETE from test 
where
out_user_code in (select * from (select out_user_code from test del group by out_user_code HAVING count(out_user_code) >1)a)
and seq_id not in(select * from (select min(seq_id) id from test del group by out_user_code  HAVING count(out_user_code) >1)b
)f

提醒:能逻辑删除尽量不要物理删除。

二、窗口函数row_number()

思路:通过 PARTITION BY 对列进行分区排序并生成序号列,然后将序号大于 1 的行删除,row_number() over partition by。

1、分区查询:

SELECT
	ROW_NUMBER() OVER ( PARTITION BY out_user_code ORDER BY seq_id ) num,
	out_user_code 
FROM
	test 
WHERE
	out_user_code IS NOT NULL

SQL删除重复的记录(只保留一条)-窗口函数row_number()

知识补充:
1、ROW_NUMBER:对结果集的输出进行编号,是运行查询时计算出的临时值。 具体来说,返回结果集分区内行的序列号,每个分区的第一行从 1 开始。
2、ROW_NUMBER() 具有不确定性。除非以下条件成立,否则不保证在每次执行时,使用 ROW_NUMBER() 的查询所返回行的顺序都完全相同。
1)分区列的值是唯一的。
2)ORDER BY 列的值是唯一的。
3)分区列和 ORDER BY 列的值的组合是唯一的。

2、直接尝试删除num>1的数据:

DELETE a 
FROM (
	SELECT
	 ROW_NUMBER() OVER (PARTITION BY out_user_code ORDER BY seq_id) num 
	FROM test 
	where out_user_code IS NOT NULL
) a 
WHERE num>1

会发现报错:
1288 - The target table a of the DELETE is not updatable, Time: 0.007000s

原因同上,同样的我们换个方式处理一下。给窗口指定别名:WINDOW w AS (PARTITION BY 字段1 ORDER BY 字段2)

3、最终处理sql:

DELETE
FROM test
WHERE seq_id in (
	SELECT seq_id
	FROM(
		SELECT *
		FROM (
			SELECT ROW_NUMBER() OVER w AS row_num,seq_id
			FROM test  where out_user_code is not null
			WINDOW w AS (PARTITION BY out_user_code ORDER BY seq_id)
		)t
	    WHERE row_num >1
	)e
)

SQL删除重复的记录(只保留一条)-窗口函数row_number()

四、补充:常见的窗口函数

SQL删除重复的记录(只保留一条)-窗口函数row_number()

注:‘参数’列说明该函数是否可以加参数。“否”说明该函数的括号内不可以加参数。
expr即可以代表字段,也可以代表在字段上的计算,比如sum(col)等。

窗口函数的一个概念是当前行,当前行属于某个窗口,窗口由over关键字用来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则有三个参数来设置窗口:文章来源地址https://www.toymoban.com/news/detail-453920.html

  • partition by子句:按照指定字段进行分区,两个分区由边界分隔,窗口函数在不同的分区内分别执行,在跨越分区边界时重新初始化。
  • order by子句:按照指定字段进行排序,窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用,也可以单独使用。
  • frame子句:当前分区的一个子集,用来定义子集的规则,通常用来作为滑动窗口使用。

到了这里,关于SQL删除重复的记录(只保留一条)-窗口函数row_number()的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Mysql 删除重复数据保留一条有效数据

    ============================== ©Copyright 蕃薯耀 2023-01-31 蕃薯耀的博客_CSDN博客-蕃薯耀分享,java,js领域博主 原理: 根据字段对数据进行分组,查询出所有分组的最小ID(即要保留的不重复数据) 将查询出来的数据(所有不重复的数据)存放到临时表中 从原来的表中删除ID不在临时表

    2024年02月16日
    浏览(50)
  • SqlServer中根据某几列获取重复的数据将其删除并保留最新一条

    有时候,我们某个数据表中,可能有几列的数据都是一样的,此时我们可能想查询出这几列数据相同的所有数据行,并保留最新一条,将其他重复的数据删除。 假设我们有如下数据表: 此时我们可以使用 ROW_NUMBER 函数,根据某几列查询出重复数据的新的排序列,该排序列就

    2024年04月28日
    浏览(39)
  • SQL中常用的窗口函数(排序函数)-row_number/rank/dense_rank/ntile

    总结四个函数的特点: row_number():连续不重复;1234567 rank() :重复不连续;1222567 dense_rank():重复且连续;1222345 ntile():平均分组;1122334 SQL Server中的排序函数有四个:row_number(),rank(),dense_rank()及ntile()函数; 特点: row_number()函数可以为每条记录添加递增的顺序数值序号,

    2024年02月05日
    浏览(217)
  • mysql中去除重复数据,只保留一条。

                    梳理一下关于删除重复记录的逻辑 目录 前期准备:建表插入数据 1、通过group by 和count(1)1找出有重复的数据  2、通过每个分组中的最小id来去重         2.1、添加主键id列         2.2 去重                 2.2.1、首先找出每个分组中count

    2024年02月08日
    浏览(109)
  • MySQL 联表查询重复数据并删除(子查询删除记录) SQL优化

    数据库表介绍: table_a :主表(小表,表数据不可重复) table_b :流水表(大表,记录审核流水数据) 注:两表表结构大致一致,流水表增加一个审核状态的字段 业务逻辑: 主表保存唯一数据,流水表记录审核流水数据,用于后续展示,并在审核成功后插入主表,在插入流

    2023年04月08日
    浏览(65)
  • [hive] 窗口函数 ROW_NUMBER()

    在 Hive SQL 中, ROW_NUMBER() 是一个用于生成行号的窗口函数。 它可以为 查询结果集中的每一行分配一个唯一的行号 。 以下是 ROW_NUMBER() 函数的基本语法: PARTITION BY 子句可选,用于指定 分区列 ,它将结果集划分为不同的分区。 每个分区内的行都会有独立的行号计数,即行号

    2024年02月07日
    浏览(38)
  • git 删除某一条提交记录

    1.首先使用git log命令查看提交记录,找到出错的那一笔提交的commit_id(黄色)   2,用命令git rebase -i commit_id ,查找提交记录   3.执行(2)命令后出现 如下界面:  4. 输入 i 进入编辑模式,在要删除的commitid 前,将pick修改成drop。看到的结果如下图:   5.编辑完成,按键盘Esc,退

    2024年02月11日
    浏览(46)
  • sql中的排序函数dense_rank(),RANK()和row_number()

    dense_rank(),RANK()和row_number()是SQL中的排序函数。 为方便后面的函数差异比对清晰直观,准备数据表如下: 1.dense_rank() 函数语法:dense_rank() over( order by 列名 【desc/asc】) DENSE_RANK()是连续排序,比如遇到相同的数值时,排序时,是1 2 2 3 序号连续的。 按照分数从高到低排名: 按

    2024年02月09日
    浏览(38)
  • SQLServer删除表中重复记录

    转载链接:https://www.bbsmax.com/A/1O5Ee12G57/ 重复记录:有两个意义上的重复记录 一是完全重复的记录,也即所有字段均重复的记录; 二是部分段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。 1、对于第一种重复,比较容易解决,使用 select d

    2024年02月01日
    浏览(51)
  • SQL Server删除重复数据的方法

    在SQL Server中,有多种方法可以删除重复数据。下面将介绍几种常用的方法,并提供相应的源代码示例。 方法一:使用DISTINCT和临时表 使用DISTINCT可以去除重复的行,我们可以将去重后的结果插入到一个临时表中,然后删除然后删除原始表中的数据,最后将临时表中的数

    2024年02月04日
    浏览(47)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包