mysql查询表中重复数据合并

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

SELECT name, email, COUNT(*)
FROM users
GROUP BY name, email
HAVING COUNT(*) > 1;

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE t1.id > t2.id AND t1.name = t2.name AND t1.email = t2.email;

START TRANSACTION;

DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE t1.id > t2.id AND t1.name = t2.name AND t1.email = t2.email;

COMMIT;

ALTER TABLE users ADD UNIQUE INDEX idx_unique_name_email (name, email);

INSERT INTO users (id, name, email)
SELECT * FROM (SELECT 1 AS id, 'John Doe' AS name, 'john@example.com' AS email) AS tmp
WHERE NOT EXISTS (
    SELECT name FROM users WHERE name = 'John Doe' AND email = 'john@example.com'
) LIMIT 1;