Mysql除重解決方案（兩種方式）

Mysql删除重複資料

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

• ​​1.1、初始化資料​​
• ​​1.2、删除重複資料​​

• ​​二、Mysql 8.0 新增函數 ROW_NUMBER()實作​​

• ​​2.1 、初始化資料，同上 1.1​​
• ​​2.2 、執行SQL​​

一、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,'[email protected]');
INSERT INTO EMPLOYEE VALUES (2,'Tl2',102,'[email protected]');
INSERT INTO EMPLOYEE VALUES (3,'Tl3',103,'[email protected]');
INSERT INTO EMPLOYEE VALUES (4,'Tl4',104,'[email protected]');

-- 重複的資料

INSERT INTO EMPLOYEE VALUES (5,'Tl1',101,'[email protected]');

INSERT INTO EMPLOYEE VALUES (6,'Tl2',102,'[email protected]');      

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