萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 查詢不重複數據並按最大 ID 排序、刪除重複數據技巧

問題場景

表 a 有重複 user,需取出 id 最大的前 4 條且 user 不重複的記錄。直接 SELECT DISTINCT user FROM a ORDER BY id DESC LIMIT 4 會受較小 id 干擾,排序不準確。

正確方案:先按用戶分組取最大 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;

1. 查找每組重複數據中最小的 ID

SELECT MIN(id) AS id
FROM t1
GROUP BY name, add
HAVING COUNT(1) > 1;

2. 查找所有重複行

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;

3. 查找除最小 ID 外的重複行(用於刪除)

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;

4. 刪除重複數據(保留最小 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;
copyright © 萬盛學電腦網 all rights reserved