如何檢索SQL中每個組的頂部N記錄？

在 SQL 中檢索每組前 N 條記錄

本指南演示如何高效地檢索 SQL 表中每個組的前 N ​​條記錄，按年齡（降序）排序，然後按人員姓名的字母順序排列以處理關係。

場景：

想像一個表格，其中包含有關人員、其所屬群體和年齡的信息：

Person	Group	Age
Bob	1	32
Jill	1	34
Shawn	1	42
Jake	2	29
Paul	2	36
Laura	2	39

目標是檢索每個組的前 2 (N=2) 條記錄，優先考慮年齡較大的個體並按字母順序解決關係。所需的輸出是：

Person	Group	Age
Shawn	1	42
Jill	1	34
Laura	2	39
Paul	2	36

解決方案：

提出了兩種常見的方法：UNION ALL 和 ROW_NUMBER() 窗口函數。

方法1：使用UNION ALL（對於大數據集效率較低）

此方法很簡單，但對於具有多個組且每個組有大量記錄的表來說可能會變得低效。 它涉及為每個組創建單獨的查詢並使用 UNION ALL 組合結果。 此示例檢索每組的前 2 條記錄 (N=2)：

(
  SELECT *
  FROM mytable
  WHERE `group` = 1
  ORDER BY age DESC, person
  LIMIT 2
)
UNION ALL
(
  SELECT *
  FROM mytable
  WHERE `group` = 2
  ORDER BY age DESC, person
  LIMIT 2
)

此方法需要針對每個附加組進行修改。 對於大量組而言，它無法擴展。

方法二：使用ROW_NUMBER()（更高效）

ROW_NUMBER() 窗口函數提供了更高效且可擴展的解決方案。它根據指定的順序為每個組中的每一行分配一個唯一的排名。

SELECT person, `group`, age
FROM (
   SELECT person, `group`, age,
      ROW_NUMBER() OVER (PARTITION BY `group` ORDER BY age DESC, person) as rn
   FROM mytable
) as ranked_data
WHERE rn <= 2;

此查詢首先為每個組中的每一行分配一個排名 (rn)，按年齡（降序）排序，然後按人員姓名排序。然後，外部查詢會篩選結果以僅包含排名小於或等於 2 (N=2) 的行。 這種方法效率更高，並且可以輕鬆適應不同的 N 值和可變數量的組。

進一步閱讀：

要全面探索選擇每組前 N 條記錄的技術，請參考此資源：https://www.php.cn/link/131632cb7eeb986974e1be59af67e8fe

以上是如何檢索SQL中每個組的頂部N記錄？的詳細內容。更多資訊請關注PHP中文網其他相關文章！