mysql删除查重复

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE
);

DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY name, email
);

CREATE TEMPORARY TABLE temp_users AS
SELECT MIN(id) as id, name, email
FROM users
GROUP BY name, email;

DELETE FROM users WHERE id NOT IN (SELECT id FROM temp_users);

DROP TEMPORARY TABLE temp_users;

WITH cte AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY id) as rn
    FROM users
)
DELETE FROM cte WHERE rn > 1;