SQL 按年分組並保持原序：ROW_NUMBER + 條件聚合實現

需求：將 (Year, Value) 按年分組，組內保持原輸入順序，並標記組序號。SQL Server 方案：

完整腳本

-- 測試數據
IF OBJECT_ID('tempdb..#tb') IS NOT NULL DROP TABLE #tb;
CREATE TABLE #tb (A INT, B INT);
INSERT #tb VALUES 
(2011,100),(2011,60),(2011,70),
(2012,200),(2012,250),
(2013,300),(2013,400);

-- 查詢
;WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn FROM #tb
),
GrpSeq AS (
    SELECT A, MIN(rn) AS first_rn
    FROM Numbered
    GROUP BY A
),
SeqRank AS (
    SELECT A, ROW_NUMBER() OVER (ORDER BY first_rn) AS GrpOrder
    FROM GrpSeq
)
SELECT n.A AS Year, n.B AS Value, s.GrpOrder
FROM Numbered n
JOIN SeqRank s ON n.A = s.A
ORDER BY n.rn;

輸出

Year  Value  GrpOrder
2011  100    1
2011  60     1
2011  70     1
2012  200    2
2012  250    2
2013  300    3
2013  400    3

關鍵技巧

• ROW_NUMBER() OVER (ORDER BY (SELECT 0)) 生成穩定原序號（不依賴特定列）。
• 兩次 ROW_NUMBER：第一次保序，第二次給組排序。
• MySQL 8.0+ 可用 ROW_NUMBER() OVER (PARTITION BY A ORDER BY rn) 簡化組內排序。