MySQL 與 SQLite GROUP BY 排序機制差異深度解析

MySQL 與 SQLite GROUP BY 排序機制差異深度解析

在未使用聚合函數（SUM、MIN、MAX 等）的情況下，GROUP BY 的排序行為在 MySQL 與 SQLite 中存在關鍵差異。這直接影響「取得每群組最新一筆資料」這類常見查詢的寫法。本文深入剖析兩者差異並提供正確解法。

核心差異：遍歷與保留策略

• MySQL：遍歷資料表時，遇到重複的 GROUP BY 欄位值會忽略後續列（保留第一筆遇到的資料）
• SQLite：遍歷資料表時，遇到重複的 GROUP BY 欄位值會覆蓋先前資料（保留最後一筆遇到的資料）

實戰場景：取得每群組最新訊息

假設表 tb_push_data 儲存推播訊息，需依 groupid 分組取得最新時間 (created_date) 的一筆資料。

MySQL 寫法（子查詢用 DESC）

SELECT * FROM (
    SELECT t.id, t.data_id, t.send_username, t.recv_username, t.message,
           IFNULL(NULLIF(t.groupid, ''), t.recv_username) AS groupid,
           t.created_date
    FROM tb_push_data t
    WHERE STATUS = 0
    ORDER BY t.created_date DESC, t.id DESC
) t1
GROUP BY t1.groupid
ORDER BY t1.created_date DESC, t1.id DESC;

SQLite 寫法（子查詢用 ASC）

SELECT * FROM (
    SELECT t.id, t.data_id, t.login_username, t.send_username, t.recv_username, t.message,
           IFNULL(NULLIF(t.groupid, ''), t.send_username) AS groupid,
           t.time
    FROM tb_recved_data t
    WHERE status = 0
    ORDER BY t.time ASC, t.id ASC
) t1
GROUP BY t1.groupid
ORDER BY t1.time DESC, t1.id DESC;

為什麼排序方向相反？

關鍵洞察：MySQL 保留「第一次遇到」的列，所以子查詢必須讓最新資料排在最前；SQLite 保留「最後一次遇到」的列，所以子查詢必須讓最新資料排在最後。

最佳實踐建議

1. 明確使用聚合函數：改用 MAX(created_date) 搭配 JOIN 或窗口函數（MySQL 8.0+），避免依賴非標準行為
2. 標準化寫法（跨資料庫相容）：

-- 使用 ROW_NUMBER() 窗口函數 (MySQL 8.0+, SQLite 3.25+)
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY groupid ORDER BY created_date DESC) AS rn
    FROM tb_push_data
    WHERE status = 0
) t WHERE rn = 1;

總結

理解 MySQL「保留首筆」與 SQLite「保留尾筆」的本質差異，是撰寫正確 GROUP BY 查詢的關鍵。現代應用建議優先使用窗口函數，獲得標準、可移植且語意清晰的解決方案。