MySQL 分組排序取前 N 條記錄：變量模擬 ROW_NUMBER 實戰

MySQL 分組排序取前 N 條記錄：變量模擬 ROW_NUMBER 實戰

在實際開發中，經常遇到「按某欄位分組，取每組前 N 條記錄」的需求。例如：按機器分組，取每台機器最新的 4 筆流量記錄。MySQL 8.0 之前不支援窗口函數，常見做法是使用用戶變量模擬 ROW_NUMBER()。

場景與表結構

CREATE TABLE `total_freq_ctrl` (
  `time` int(10) unsigned NOT NULL,
  `machine` char(64) NOT NULL,
  `module` char(32) NOT NULL,
  `total_flow` int(10) unsigned NOT NULL,
  `deny_flow` int(10) unsigned NOT NULL,
  PRIMARY KEY (`module`,`machine`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

原始方案：相關子查詢

SELECT machine, deny_flow, total_flow, time 
FROM total_freq_ctrl A 
WHERE 1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) 
  AND A.module = 'all' 
ORDER BY A.time desc;

將 1 改為 N 即可取前 N 條，但子查詢效率低，且難以優化。

優化方案：用戶變量生成分組行號

核心思路：先按分組欄位、排序欄位排序，再用 @row 計數、@mid 記錄上一組值，實現分組內遞增編號。

步驟 1：基礎行號

SET @row=0;
SELECT module, machine, time, @row:=@row+1 AS rownum
FROM total_freq_ctrl
ORDER BY module, machine, time DESC
LIMIT 10;

步驟 2：分組行號（關鍵）

SET @row=0; SET @mid='';
SELECT module, machine, time,
       CASE WHEN @mid = machine THEN @row:=@row+1 ELSE @row:=1 END AS rownum,
       @mid:=machine
FROM total_freq_ctrl
ORDER BY module, machine, time DESC
LIMIT 20;

關鍵點：ORDER BY module, machine, time DESC 確保同一機器的記錄連續、時間倒序；CASE WHEN 判斷機器是否變化，變化則重置計數器。

步驟 3：外層關聯過濾

SET @row=0; SET @mid='';
SELECT a.*, b.rownum
FROM total_freq_ctrl a
INNER JOIN (
  SELECT module, machine, time,
         CASE WHEN @mid = machine THEN @row:=@row+1 ELSE @row:=1 END AS rownum,
         @mid:=machine AS mid
  FROM total_freq_ctrl
  ORDER BY module, machine, time DESC
) b ON a.module=b.module AND a.machine=b.machine AND a.time=b.time
WHERE b.rownum < 5;

結果即為每台機器最新的 4 筆記錄（rownum 1~4）。

注意事項

• MySQL 8.0+ 建議直接使用 ROW_NUMBER() OVER (PARTITION BY machine ORDER BY time DESC)，語義清晰且優化器支援更好。
• 變量法依賴執行計劃的順序，複雜查詢（如含 JOIN、子查詢）可能導致行號錯亂，生產環境請充分測試。
• 必須先 SET @row=0; SET @mid=''; 初始化，否則變量殘留會導致結果錯誤。

總結

用戶變量模擬 ROW_NUMBER 是 MySQL 5.7 時代解決「分組 Top-N」的經典技巧，核心在於「排序 + 變量計數 + 分組邊界判斷」。理解其原理有助於遷移到窗口函數，也能應對不支援窗口函數的舊版本環境。