「按分組取第一條」是 Top-N per Group 經典問題,常見於:每個用戶最新訂單、每個商品最高價、每日首條日誌等場景。本文提供 SQL Server、Oracle、MySQL、PostgreSQL 四大主流數據庫的標準寫法、性能對比、避坑指南,並統一解釋底層邏輯。
無論哪個數據庫,本質都是三步:
SELECT T1.*
FROM Groups G
CROSS APPLY (
SELECT TOP 1 *
FROM Table1 T
WHERE T.Grp = G.Grp
ORDER BY CreateTime DESC
) T1;
INDEX IX_Table1_Grp_CreateTime (Grp, CreateTime DESC) INCLUDE (其他列)WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
FROM Table1
)
SELECT * FROM Ranked WHERE RN = 1;
SELECT * FROM Table1 T WHERE ID = (SELECT TOP 1 ID FROM Table1 WHERE Grp = T.Grp ORDER BY CreateTime DESC);
SELECT * FROM (
SELECT
Type, Code,
ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Code) AS RN
FROM Group_Info
) WHERE RN = 1;
SELECT GI.*
FROM Group_Info GI
JOIN (
SELECT Type, MIN(Code) AS MinCode FROM Group_Info GROUP BY Type
) T ON GI.Type = T.Type AND GI.Code = T.MinCode;
ANALYTIC_FUNCTION([args]) OVER (
[PARTITION BY partition_columns] -- 分組,類比 GROUP BY
[ORDER BY order_columns] -- 組內排序
[WINDOWING_CLAUSE] -- 滑動窗口 (可選)
)
WITH Ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
FROM Table1
)
SELECT * FROM Ranked WHERE RN = 1;
SELECT Grp, Col1, Col2 FROM (
SELECT
T.*,
@rn := IF(@prev = Grp, @rn + 1, 1) AS RN,
@prev := Grp
FROM Table1 T
CROSS JOIN (SELECT @rn := 0, @prev := NULL) VARS
ORDER BY Grp, CreateTime DESC
) T WHERE RN = 1;
-- 僅取全表第一條 (無分組) SELECT * FROM Table1 ORDER BY CreateTime DESC LIMIT 1; -- 分組場景不適用 LIMIT,必須用窗口函數或變量模擬
SELECT DISTINCT ON (Grp) * FROM Table1 ORDER BY Grp, CreateTime DESC;
DISTINCT ON (分組列) 保留每組第一行,**必須配合 ORDER BY 保證組內排序**SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
FROM Table1
) T WHERE RN = 1;
| 數據庫 | 推薦方案 | 關鍵索引 | 適用數據量 |
|---|---|---|---|
| SQL Server | CROSS APPLY / ROW_NUMBER | (Grp, CreateTime DESC) INCLUDE (需求列) | 百萬級+ |
| Oracle | ROW_NUMBER / DISTINCT ON 等價寫法 | (Grp, CreateTime DESC) | 億級 |
| MySQL 8.0+ | ROW_NUMBER | (Grp, CreateTime DESC) | 千萬級 |
| PostgreSQL | DISTINCT ON (首選) / ROW_NUMBER | (Grp, CreateTime DESC) | 億級 |
GROUP BY Grp HAVING MIN(CreateTime) 然後 JOIN 回原表 → 並列首行時多行、性能差GROUP BY Grp 直接 SELECT * → ONLY_FULL_GROUP_BY 報錯或結果不確定分組列 + 排序列,順序固定統一認知:現代 SQL 標準 (SQL:2003) 確立的窗口函數,已成為跨數據庫解決 Top-N per Group 的統一語言。掌握
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)這一模式,即可在 90% 的關係型數據庫中高效解決問題。