Mysql删除重复数据

一、DELETE e1 FROM Emp e1, Empe2 WHERE e1.name = e2.name AND e1.id > e2.id;

1.1、初始化数据

CREATE TABLE EMPLOYEE
(
  ID INT,
  NAME Varchar(100),
  DEPARTMENT INT,
  EMAIL Varchar(100)
);

INSERT INTO EMPLOYEE VALUES (1,'Tl1',101,'Tl1@ls.com');
INSERT INTO EMPLOYEE VALUES (2,'Tl2',102,'Tl2h@ls.com');
INSERT INTO EMPLOYEE VALUES (3,'Tl3',103,'Tl3h@ls.com');
INSERT INTO EMPLOYEE VALUES (4,'Tl4',104,'Tl4h@ls.com');

-- 重复的数据

INSERT INTO EMPLOYEE VALUES (5,'Tl1',101,'Tl1@ls.com');

INSERT INTO EMPLOYEE VALUES (6,'Tl2',102,'Tl2h@ls.com');

1.2、删除重复数据

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;

验证一下，即可得到

select * from EMPLOYEE;

二、Mysql 8.0 新增函数 ROW_NUMBER()实现

2.1 、初始化数据，同上 1.1

2.2 、执行SQL

WITH temp AS(SELECT 
    ID,
    NAME,
    ROW_NUMBER() OVER (PARTITION BY NAME, DEPARTMENT ORDER BY id) AS row_num
FROM EMPLOYEE) SELECT temp.id,temp.name FROM temp WHERE temp.row_num=1