[SQL] MSSQL 使用 ROW_NUMBER 替代 GroupBy

如題，之前寫過許多類似情境但未紀錄，所以本次也記錄一下。該需求主要是"找出多個類別中特定條件的資料"，聽起來很複雜，實際案例如: ＜找出每個員工最新的銷售紀錄＞，這種情境若使用Group By寫會非常複雜，因為Group By需要聚合函數，所以可以使用ROW_NUMBER 替代 Group By。

不廢話，以下為範例程式碼：

-- 建立 Temp Table
CREATE TABLE #EmployeeSales (
    EmployeeID INT,
    EmployeeName NVARCHAR(50),
    SaleDate DATE,
    SaleAmount DECIMAL(10, 2)
);

-- 插入測試數據
INSERT INTO #EmployeeSales (EmployeeID, EmployeeName, SaleDate, SaleAmount)
VALUES 
(1, 'Alice', '2023-05-01', 500.00),
(1, 'Alice', '2023-05-05', 800.00),
(1, 'Alice', '2023-05-10', 200.00),
(2, 'Bob', '2023-05-01', 300.00),
(2, 'Bob', '2023-05-03', 700.00),
(3, 'Charlie', '2023-05-01', 400.00),
(3, 'Charlie', '2023-05-08', 600.00),
(3, 'Charlie', '2023-05-10', 900.00);

-- 使用 ROW_NUMBER() 實現類似 GROUP BY 效果
SELECT EmployeeID, EmployeeName, SaleDate, SaleAmount
FROM (
    SELECT 
        EmployeeID,
        EmployeeName,
        SaleDate,
        SaleAmount,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY SaleDate DESC) AS RowNum
    FROM #EmployeeSales
) AS RankedSales
WHERE RowNum = 1;

補充: 這邊的篩選會依靠PARTITION BY跟ORDER BY

● PARTITION BY 可定義[不重複的鍵值]，本案例為定義"每個員工"。

● ORDER BY 可定義篩選標準，本案例為定義"最新"的銷售紀錄。

若要改為每位員工最高的銷售紀錄，那可以改為

ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY SaleAmount DESC) AS RowNum