表 device 中 device_id 重複,需保留最新一條(按 create_date),刪除其餘。
SELECT device_id, COUNT(*) AS cnt
FROM device
GROUP BY device_id
HAVING COUNT(*) > 1;
SELECT userid, device_id, create_date
FROM device
WHERE device_id IN (
SELECT device_id FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
ORDER BY device_id, create_date DESC;
SELECT device_id, MAX(create_date) AS keep_date
FROM device
GROUP BY device_id
HAVING COUNT(*) > 1;
SELECT *
FROM device
WHERE device_id IN (
SELECT device_id FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
AND create_date NOT IN (
SELECT MAX(create_date) FROM device GROUP BY device_id HAVING COUNT(*) > 1
)
ORDER BY device_id, create_date DESC;
CREATE TABLE test1 (
ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
SENDERNAME VARCHAR(32),
RECEIVERNAME VARCHAR(64),
SENDER VARCHAR(64) NOT NULL,
RECEIVER VARCHAR(64) NOT NULL,
SUBJECT VARCHAR(512) NOT NULL,
CONTENT TEXT NOT NULL,
PRIORITY INT, STATUS INT,
CREATETIME DATETIME, SENDTIME DATETIME
);
SELECT a.ID, a.SUBJECT, a.RECEIVER
FROM test1 a
LEFT JOIN (
SELECT c.SUBJECT, c.RECEIVER, MAX(c.ID) AS bid
FROM test1 c
WHERE status = 0
GROUP BY RECEIVER, SUBJECT
HAVING COUNT(1) > 1
) b ON a.ID < b.bid
WHERE a.SUBJECT = b.SUBJECT
AND a.RECEIVER = b.RECEIVER
AND a.ID < b.bid;
DELETE a
FROM test1 a,
(SELECT c.SUBJECT, c.RECEIVER, MAX(c.ID) AS bid
FROM test1 c
WHERE status = 0
GROUP BY RECEIVER, SUBJECT
HAVING COUNT(1) > 1) b
WHERE a.SUBJECT = b.SUBJECT
AND a.RECEIVER = b.RECEIVER
AND a.ID < b.bid;
ALTER TABLE test1 ADD INDEX idx_sub_rec (SUBJECT, RECEIVER);LIMIT 1000 循環執行,避免長事務鎖表pt-archiver (Percona Toolkit) 低影響歸檔/刪除
-- 保留最新 (MAX(create_time))
DELETE t1 FROM tbl t1
JOIN (
SELECT dup_col, MAX(create_time) AS keep_time
FROM tbl GROUP BY dup_col HAVING COUNT(*) > 1
) t2 ON t1.dup_col = t2.dup_col AND t1.create_time < t2.keep_time;
-- 保留最大 ID
DELETE t1 FROM tbl t1
JOIN (
SELECT dup_col, MAX(id) AS keep_id
FROM tbl GROUP BY dup_col HAVING COUNT(*) > 1
) t2 ON t1.dup_col = t2.dup_col AND t1.id < t2.keep_id;