萬盛學電腦網

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

MySQL 分組取每組最新記錄:高效 SQL 與存儲過程排名實現

場景

表 test:id(PK), install, day(日期整數), aid(分組鍵)。需求:按 aid 分組,取每組 day 最大(最新)的那行完整記錄,再按 install 降序排列。

標準解法:衍生表 JOIN(全版本通用、效能佳)

SELECT t1.*
FROM `test` AS t1
JOIN (
  SELECT aid, MAX(day) AS max_day
  FROM `test`
  GROUP BY aid
) AS t2 ON t1.aid = t2.aid AND t1.day = t2.max_day
ORDER BY t1.install DESC;
  • 衍生表 t2 只有 aid, max_day 兩列,極小
  • JOIN 走 aid 索引(建議建立 INDEX(aid, day))
  • 若組內有多行同為最大 day,會全部返回

進階:MySQL 8.0 窗口函數(最優雅)

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY aid ORDER BY day DESC, id DESC) AS rn
  FROM `test`
)
SELECT * FROM ranked WHERE rn = 1 ORDER BY install DESC;
  • ROW_NUMBER() 保證每組僅取 1 行,配合 id DESC 做確定性去重
  • 執行計畫優化佳,無需自建衍生表

存儲過程:分組排序並賦予排名(歷史版本無窗口函數時)

DROP PROCEDURE IF EXISTS set_rank;

DELIMITER //
CREATE PROCEDURE set_rank()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE total INT;
  
  SELECT COUNT(*) INTO total FROM suppliers_performance;
  
  UPDATE suppliers_performance SET score_rank = 0;
  
  WHILE i <= total DO
    UPDATE suppliers_performance 
    SET score_rank = i 
    WHERE id IN (
      SELECT id FROM (
        SELECT id FROM suppliers_performance 
        WHERE score_rank = 0 
        ORDER BY score LIMIT 1
      ) AS t
    );
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;

邏輯:循環將未排名記錄按 score 排序,每次取第一名賦予遞增排名,直到全部排名完成。

⚠️ 效能警告:此過程對大表極慢(O(N²)),僅供學習參考。生產環境請用窗口函數或應用層計算。

索引建議

-- 方案一:支援衍生表 JOIN
CREATE INDEX idx_aid_day ON test(aid, day);

-- 方案二:支援窗口函數/覆蓋索引
CREATE INDEX idx_aid_day_install ON test(aid, day DESC, install DESC);

常見誤區

SELECT *, MAX(day) FROM test GROUP BY aid
非聚合欄位 * 值不確定(MySQL 5.7+ 預設報錯),不可用。
HAVING day = MAX(day)
僅當 day 唯一對應最大值時正確,否則返回多行或錯行。
copyright © 萬盛學電腦網 all rights reserved