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

问题描述&＃xff1a;

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

解决方案&＃xff1a;

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

-- 删除主表 col是本表的主键

DELETE FROM tmp101 A

WHERE col NOT IN (SELECT MIN(col)

FROM tmp101 b

WHERE A.col1 &＃61; b.col1

AND A.col2 &＃61; b.col2

AND A.col3 &＃61; b.col3

AND A.col4 &＃61; 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 &＃61; b.col1 AND a.col2 &＃61; b.col2

AND a.col3 &＃61; b.col3 AND a.col4 &＃61; b.col4

AND a.col > b.col);

[Err] 1093 - You can&＃39;t specify target table &＃39;tmp101&＃39; for update in FROM clause

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

DELETE FROM tmp101 WHERE col1 in(

SELECT * FROM(

SELECT DISTINCT(a.col1) FROM tmp101 a

INNER JOIN tmp101 b

ON a.col1 &＃61; b.col1 AND a.col2 &＃61; b.col2

AND a.col3 &＃61; b.col3 AND a.col4 &＃61; b.col4

AND a.col > b.col)a);

题外话&＃xff1a;

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

实现思路如下&＃xff1a;

SELECT

IF(t1.id &＃61; &＃64;id and t1.name &＃61; &＃64;name,&＃64;rownum :&＃61; &＃64;rownum &＃43; 1,&＃64;rownum :&＃61; 1)AS rownum,

t1.*,

&＃64;id :&＃61; t1.id,

&＃64;name :&＃61; t1.name

FROM

(SELECT &＃64;rownum :&＃61; 0,&＃64;id :&＃61; NULL,&＃64;name :&＃61; NULL) r,

test1 t1

ORDER BY t1.id,t1.name,t1.starttime DESC;

语法解析&＃xff1a;

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

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

方案三&＃xff1a;实现row_number

DELETE FROM tmp101 WHERE col1 in(

select col from(

select a.*,&＃64;rownum:&＃61;&＃64;rownum&＃43;1 rownum,

IF(&＃64;col1 &＃61; a.col1 AND &＃64;col2 &＃61; a.col2 AND &＃64;col3 &＃61; a.col3 AND &＃64;col4 &＃61; a.col4 ,

&＃64;rank :&＃61; &＃64;rank &＃43; 1,

&＃64;rank :&＃61; 1) AS rank,

&＃64;col1 :&＃61; a.col1 , &＃64;col2 :&＃61; a.col2 , &＃64;col3 :&＃61; a.col3 , &＃64;col4 :&＃61; a.col4

from (SELECT * FROM tmp101 ORDER BY col1,col2,col3,col4) a,

(select &＃64;col1 :&＃61; null,&＃64;col2 :&＃61; null,&＃64;col3 :&＃61; null,&＃64;col4 :&＃61; null,

&＃64;rownum:&＃61;null,&＃64;rank :&＃61; null) tmp

) a WHERE rank <> 1) a;

小故事&＃xff1a;

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

下面简单科普下

MySQL的&＃61;和:&＃61;的区别。

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

不只在set和update时起到赋值的作用&＃xff0c;在select也是赋值的作用。

简单来说就是&＃61;是等于的作用&＃xff0c;判断相等。

:&＃61;是赋值的作用。

如果用混了会导致结果不正确&＃xff01;