萬盛學電腦網

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

SQL 獲取每組第一條記錄的跨數據庫標準寫法:SQL Server、Oracle、MySQL、PostgreSQL 全覆蓋

「按分組取第一條」是 Top-N per Group 經典問題,常見於:每個用戶最新訂單、每個商品最高價、每日首條日誌等場景。本文提供 SQL Server、Oracle、MySQL、PostgreSQL 四大主流數據庫的標準寫法、性能對比、避坑指南,並統一解釋底層邏輯。

🎯 核心邏輯:分組 + 排序 + 取首

無論哪個數據庫,本質都是三步:

  1. 分組:按業務鍵分區
  2. 排序:組內按業務時間/優先級排序
  3. 取首:每組取第 1 行

🏢 SQL Server:三種主流方案

方案一:CROSS APPLY (推薦,性能最優)

SELECT T1.*
FROM Groups G
CROSS APPLY (
    SELECT TOP 1 * 
    FROM Table1 T 
    WHERE T.Grp = G.Grp 
    ORDER BY CreateTime DESC
) T1;
  • 語義清晰:外層驅動表(分組鍵去重表或小表)+ 內層 Top 1 索引查找
  • 需索引:INDEX IX_Table1_Grp_CreateTime (Grp, CreateTime DESC) INCLUDE (其他列)

方案二:ROW_NUMBER() 窗口函數 (SQL Server 2012+)

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
    FROM Table1
)
SELECT * FROM Ranked WHERE RN = 1;
  • 通用性強、可擴展取 Top-N、現代優化器對窗口函數優化極好

方案三:相關子查詢 (舊版本兼容)

SELECT * FROM Table1 T
WHERE ID = (SELECT TOP 1 ID FROM Table1 WHERE Grp = T.Grp ORDER BY CreateTime DESC);
  • 直觀但易產生嵌套循環,大數據量性能不穩定

☀️ Oracle:分析函數原生優勢

方案一:ROW_NUMBER() OVER (標準、高效)

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;
  • 僅當「第一條」定義為單列極值且無並列時適用

Oracle 分析函數語法結構

ANALYTIC_FUNCTION([args]) OVER (
    [PARTITION BY partition_columns]  -- 分組,類比 GROUP BY
    [ORDER BY order_columns]          -- 組內排序
    [WINDOWING_CLAUSE]                -- 滑動窗口 (可選)
)

🐬 MySQL:版本分野與最佳實踐

MySQL 8.0+:窗口函數原生支援 (首選)

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
    FROM Table1
)
SELECT * FROM Ranked WHERE RN = 1;

MySQL 5.7 及以下:變量模擬行號 (技巧性寫法)

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;
  • ⚠️ 依賴變量求值順序,非標準 SQL,8.0+ 已棄用,務必遷移窗口函數

簡單場景:LIMIT 配合子查詢

-- 僅取全表第一條 (無分組)
SELECT * FROM Table1 ORDER BY CreateTime DESC LIMIT 1;

-- 分組場景不適用 LIMIT,必須用窗口函數或變量模擬

🐘 PostgreSQL:功能最豐富、語法最優雅

方案一:DISTINCT ON (PostgreSQL 獨有,極簡高效)

SELECT DISTINCT ON (Grp) *
FROM Table1
ORDER BY Grp, CreateTime DESC;
  • DISTINCT ON (分組列) 保留每組第一行,**必須配合 ORDER BY 保證組內排序**
  • 執行計劃通常優於窗口函數,是 PG 獨家殺手鐧

方案二:標準窗口函數

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Grp ORDER BY CreateTime DESC) AS RN
    FROM Table1
) T WHERE RN = 1;

⚡ 性能對比與索引設計通則

數據庫推薦方案關鍵索引適用數據量
SQL ServerCROSS APPLY / ROW_NUMBER(Grp, CreateTime DESC) INCLUDE (需求列)百萬級+
OracleROW_NUMBER / DISTINCT ON 等價寫法(Grp, CreateTime DESC)億級
MySQL 8.0+ROW_NUMBER(Grp, CreateTime DESC)千萬級
PostgreSQLDISTINCT ON (首選) / ROW_NUMBER(Grp, CreateTime DESC)億級

🛠️ 避坑指南

  • ❌ GROUP BY Grp HAVING MIN(CreateTime) 然後 JOIN 回原表 → 並列首行時多行、性能差
  • ❌ MySQL 5.7 用 GROUP BY Grp 直接 SELECT * → ONLY_FULL_GROUP_BY 報錯或結果不確定
  • ❌ 忽略並列情況(同組同時間多行)→ 明確業務規則:取最小 ID?隨機?全部?
  • ✅ 索引必須包含 分組列 + 排序列,順序固定
  • ✅ 大表考慮物化視圖/汇總表預計算 Top-1

統一認知:現代 SQL 標準 (SQL:2003) 確立的窗口函數,已成為跨數據庫解決 Top-N per Group 的統一語言。掌握 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 這一模式,即可在 90% 的關係型數據庫中高效解決問題。

copyright © 萬盛學電腦網 all rights reserved