ITPub博客

首页 > 数据库 > MySQL > MySQL删除重复记录并保留第一条

MySQL删除重复记录并保留第一条

原创 MySQL 作者:myis55555 时间:2021-01-14 15:31:25 0 删除 编辑

问题描述:

有一张表因为没有加重复判断,导致会有些重复数据存在,现在需要根据重复规则去掉重复的记录,只保留第一条。

解决方案:

1、两表自关联后按照主键删除,类似于Oracle的rowid

-- 删除主表 col是本表的主键                  
DELETE FROM tmp101 A
 WHERE col NOT IN (SELECT MIN(col)
                   FROM tmp101 b
                  WHERE A.col1 = b.col1
                    AND A.col2 = b.col2
                    AND A.col3 = b.col3
                    AND A.col4 = b.col4);


2、采用inner join的方式找出需要删掉的数据

DELETE FROM tmp101 WHERE col1 in(
SELECT DISTINCT(a.col1) FROM tmp101 a
INNER JOIN tmp101 b
ON a.col1 = b.col1 AND a.col2 = b.col2 
AND a.col3 = b.col3 AND a.col4 = b.col4
AND a.col > b.col);

[Err] 1093 - You can't specify target table 'tmp101' for update in FROM clause

MySQL就有一个很尴尬的地方,就是必须要把表包起来,才不报错。如下

DELETE FROM tmp101 WHERE col1 in(
SELECT * FROM(
SELECT DISTINCT(a.col1) FROM tmp101 a
INNER JOIN tmp101 b
ON a.col1 = b.col1 AND a.col2 = b.col2 
AND a.col3 = b.col3 AND a.col4 = b.col4
AND a.col > b.col)a);

题外话:

在mysql8.0下是不支持row_number()over(partition by  order by)的。

实现思路如下:

SELECT
IF(t1.id = @id and t1.name = @name,@rownum := @rownum + 1,@rownum := 1)AS rownum,
t1.*,
@id := t1.id,
@name := t1.name
FROM
(SELECT @rownum := 0,@id := NULL,@name := NULL) r,
test1 t1
ORDER BY t1.id,t1.name,t1.starttime DESC;

语法解析:

以id和starttime进行排序可以理解为id分组内starttime排序;

test1表关联@rownum :=0,@id :=null,在select中if判断@id是否等于当前行的id,因为初始为null,所以@rownum :=1,当id变化的时候,@id还是上一个id的值,此时@rownum重置为1,注意@id :=t1.id一定要放在if判断后面。

方案三:实现row_number

DELETE FROM tmp101 WHERE col1 in(
select col from(
select a.*,@rownum:=@rownum+1 rownum,
IF(@col1 = a.col1 AND @col2 = a.col2 AND @col3 = a.col3 AND @col4 = a.col4 ,
@rank := @rank + 1,
@rank := 1) AS rank,
@col1 := a.col1 , @col2 := a.col2 , @col3 := a.col3 , @col4 := a.col4
from (SELECT * FROM tmp101 ORDER BY col1,col2,col3,col4) a,
(select @col1 := null,@col2 := null,@col3 := null,@col4 := null,
@rownum:=null,@rank := null) tmp
) a WHERE rank <> 1) a;

小故事:

初次改写这个语句的时候,因为没有分清MySQL的=和:=导致查询出来解决不对。

下面简单科普下 MySQL的=和:=的区别。

=只有在set和update时才是和:=一样,赋值的作用,其它都是等于的作用。鉴于此,用变量实现行号时,必须用:=

不只在set和update时起到赋值的作用,在select也是赋值的作用。

简单来说就是=是等于的作用,判断相等。 :=是赋值的作用。

如果用混了会导致结果不正确!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69903557/viewspace-2750028/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-12-22

  • 博文量
    17
  • 访问量
    6578