问题描述：

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

解决方案：

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

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

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