文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2023-08-30 13:51

关注

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

测试数据准备:首先创建一个测试表test,插入一些测试数据,模拟一些重复数据(最终目标:删除重复数据,但不处理null行)

先查询下重复数据,确认待处理数据的数量,然后开始处理:

SELECT        seq_id,        out_user_code,        COUNT( out_user_code ) countFROM        testWHERE        is_deleted = 0        AND out_user_code IS NOT NULLGROUP BY        out_user_codeHAVING        count( out_user_code )> 1

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

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

SELECT    min(seq_id) seq_id,out_user_code,COUNT( out_user_code ) count FROMtest GROUP BYout_user_code 

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

DELETE from test where r.seq_id not in (SELECTmin(t.seq_id) seq_idFROMtest  tGROUP BYt.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一遍。

最终处理sql:

DELETE from test where seq_id not in (SELECT r.seq_id from (SELECT   min(t.seq_id) seq_idFROMtest  tGROUP BYt.out_user_code)  r ) and out_user_code is not null

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

DELETE from test whereout_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

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

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

分区查询:

SELECTROW_NUMBER() OVER ( PARTITION BY out_user_code ORDER BY seq_id ) num,out_user_code FROMtest WHEREout_user_code IS NOT NULL

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

直接尝试删除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)

最终处理sql:

DELETEFROM testWHERE seq_id in (SELECT seq_idFROM(SELECT *FROM (SELECT ROW_NUMBER() OVER w AS row_num,seq_idFROM test  where out_user_code is not nullWINDOW w AS (PARTITION BY out_user_code ORDER BY seq_id))t    WHERE row_num >1)e)

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

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

来源地址:https://blog.csdn.net/weixin_47061482/article/details/130726158

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-人工智能
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯