mysql取重复的第一条_MySQL删除重复记录并保留第一条

问题描述：

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

解决方案：

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也是赋值的作用。

简单来说就是=是等于的作用，判断相等。

:=是赋值的作用。

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