表 a 有重複 user,需取出 id 最大的前 4 條且 user 不重複的記錄。直接 SELECT DISTINCT user FROM a ORDER BY id DESC LIMIT 4 會受較小 id 干擾,排序不準確。
SELECT a.user
FROM a
JOIN (
SELECT user, MAX(id) AS max_id
FROM a
GROUP BY user
) AS t ON a.user = t.user AND a.id = t.max_id
ORDER BY a.id DESC
LIMIT 4;
這樣能保證按每個用戶的最大 ID 降序排列,結果穩定。
CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
add VARCHAR(20)
) ENGINE=InnoDB;
SELECT MIN(id) AS id
FROM t1
GROUP BY name, add
HAVING COUNT(1) > 1;
SELECT t1.*
FROM t1
JOIN (
SELECT name, add
FROM t1
GROUP BY name, add
HAVING COUNT(1) > 1
) AS t2 ON t1.name = t2.name AND t1.add = t2.add;
SELECT t1.*
FROM t1
JOIN (
SELECT MIN(id) AS id, name, add
FROM t1
GROUP BY name, add
HAVING COUNT(1) > 1
) AS t2 ON t1.name = t2.name AND t1.add = t2.add AND t1.id <> t2.id;
DELETE t1
FROM t1
JOIN (
SELECT MIN(id) AS id, name, add
FROM t1
GROUP BY name, add
HAVING COUNT(1) > 1
) AS t2 ON t1.name = t2.name AND t1.add = t2.add AND t1.id <> t2.id;