萬盛學電腦網

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

SQL Server 分組取每組第一筆:ROW_NUMBER() OVER (PARTITION BY) 實戰範例

需求:依 flag_3 分組,按 createtime DESC 排序,取每組最新一筆。

標準寫法 (SQL Server 2005+)

WITH ranked AS (
  SELECT
    workh.corp_code, workh.corp_name, workh.begin_date, workh.createtime,
    workh.flag_1, workh.flag_2,
    workb.main_id, workb.production_quantity, workb.spare_capacity,
    workb.equipment_code, workb.flag_3,
    ROW_NUMBER() OVER (PARTITION BY workb.flag_3 ORDER BY workh.createtime DESC) AS rn
  FROM imp_workorderh workh
  RIGHT JOIN imp_workorderb workb ON workb.main_id = workh.id
  WHERE workh.teachingplanid = '0000AA10000000016F6U'
)
SELECT *
FROM ranked
WHERE rn = 1;

關鍵點解析

  • PARTITION BY workb.flag_3:依旗標分組。
  • ORDER BY workh.createtime DESC:組內按建立時間倒序,最新排第一。
  • ROW_NUMBER():為每組產生 1,2,3... 序號。
  • 外層 WHERE rn = 1 保留各組首筆。

替代寫法 (適用舊版 / 簡單場景)

-- 相關子查詢 (效能較差)
SELECT * FROM imp_workorderb b
WHERE b.main_id = (
  SELECT TOP 1 h.id FROM imp_workorderh h
  WHERE h.id = b.main_id
  ORDER BY h.createtime DESC
);

效能建議

  • 確保 PARTITION BY 與 ORDER BY 欄位有合適索引 (如 INDEX(flag_3, createtime DESC))。
  • 大資料量時 ROW_NUMBER() 需排序,觀察 Sort 運算子是否溢寫 TempDB。
  • 若只需「最大/最小值」不需完整列,可改用 MAX() + JOIN 或 APPLY (OUTER APPLY) 更輕量。
copyright © 萬盛學電腦網 all rights reserved