SQL Server 開窗函數：按月、料號、倉庫查詢最後一次入庫數量

需求

表 ST_StockRecord 含字段：BillDate（日期）、MaterialNo（料號）、SHID（倉庫）、StockQty（出入數量）。需按**月、料號、倉庫**分組，取每組**最後一筆**（按日期倒序）的記錄，輸出日期、料號、倉庫、數量。

最佳方案：ROW_NUMBER() 開窗函數

SELECT BillDate AS 日期,
       MaterialNo AS 料號,
       SHID AS 倉庫,
       StockQty AS 出入數量
FROM (
    SELECT BillDate, MaterialNo, SHID, StockQty,
           ROW_NUMBER() OVER (
               PARTITION BY YEAR(BillDate), MONTH(BillDate), MaterialNo, SHID
               ORDER BY BillDate DESC
           ) AS rn
    FROM ST_StockRecord
) t
WHERE rn = 1
ORDER BY BillDate, MaterialNo, SHID;

關鍵點解析

1. PARTITION BY：按年、月、料號、倉庫分組（題目要求「每月」）。
2. ORDER BY BillDate DESC：組內按日期降序，最新記錄排第一。
3. ROW_NUMBER()：為組內每行分配唯一序號 1,2,3...，rn=1 即各組最新記錄。
4. 外層 WHERE rn=1：過濾出各組第一行。

進階：處理「同一時間、同倉庫、同料號」多筆並列

若存在完全相同 BillDate 的多筆，ROW_NUMBER 會任意取一筆。若需確定性（如取 ID 最大），改用：

ROW_NUMBER() OVER (
    PARTITION BY YEAR(BillDate), MONTH(BillDate), MaterialNo, SHID
    ORDER BY BillDate DESC, ID DESC  -- 假設有自增 ID
) AS rn

替代方案對比（SQL Server 2012+）

索引建議

CREATE INDEX IX_StockRecord_Query
ON ST_StockRecord (MaterialNo, SHID, BillDate DESC)
INCLUDE (StockQty);

覆蓋索引可避免回表，大幅提升分組排序效能。

開窗函數是解決「分組取 Top-N」問題的瑞士軍刀，掌握 PARTITION BY + ORDER BY + ROW_NUMBER 模式，應對各種「每組最新/最大/最小」需求遊刃有餘。