SQL Server 取得多欄位中的最大值與最小值

場景

單一資料列中有多個數值欄位（如 Score1, Score2, Score3, Score4），需快速求出該列的最大值與最小值。

方法一：VALUES 表值建構子 (SQL Server 2008+)

SELECT 
    StudentID,
    (SELECT MAX(v) FROM (VALUES (Score1), (Score2), (Score3), (Score4)) AS t(v)) AS MaxScore,
    (SELECT MIN(v) FROM (VALUES (Score1), (Score2), (Score3), (Score4)) AS t(v)) AS MinScore
FROM dbo.StudentScores;

優點：語法標準、可讀性高、自動忽略 NULL（MAX/MIN 忽略 NULL）。

方法二：CROSS APPLY + VALUES (可複用邏輯)

SELECT 
    s.StudentID,
    ca.MaxScore,
    ca.MinScore
FROM dbo.StudentScores s
CROSS APPLY (
    SELECT MAX(v) AS MaxScore, MIN(v) AS MinScore
    FROM (VALUES (s.Score1), (s.Score2), (s.Score3), (s.Score4)) AS t(v)
) ca;

優點：只掃描一次 VALUES 集合，效能略優；易於擴充（如同時求 AVG、COUNT）。

方法三：UNPIVOT (舊版相容)

SELECT StudentID, MAX(Score) AS MaxScore, MIN(Score) AS MinScore
FROM dbo.StudentScores
UNPIVOT (Score FOR Subject IN (Score1, Score2, Score3, Score4)) AS u
GROUP BY StudentID;

缺點：UNPIVOT 會過濾 NULL 值，若需保留 NULL 語意需額外處理。

方法四：CASE 運算式 (全版本通用，但冗長)

SELECT 
    StudentID,
    CASE 
        WHEN Score1 >= Score2 AND Score1 >= Score3 AND Score1 >= Score4 THEN Score1
        WHEN Score2 >= Score3 AND Score2 >= Score4 THEN Score2
        WHEN Score3 >= Score4 THEN Score3
        ELSE Score4
    END AS MaxScore,
    CASE 
        WHEN Score1 <= Score2 AND Score1 <= Score3 AND Score1 <= Score4 THEN Score1
        WHEN Score2 <= Score3 AND Score2 <= Score4 THEN Score2
        WHEN Score3 <= Score4 THEN Score3
        ELSE Score4
    END AS MinScore
FROM dbo.StudentScores;

缺點：欄位超過 4-5 個時極難維護，NULL 比較需加 ISNULL/COALESCE。

效能比較 (100 萬列測試)

擴充：動態欄位清單

若欄位名稱不固定，可用動態 SQL 組合 VALUES 清單：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(name), '), (') 
FROM sys.columns 
WHERE object_id = OBJECT_ID('dbo.StudentScores') 
  AND name LIKE 'Score%' 
  AND system_type_id IN (56, 106, 108, 52, 48); -- int, decimal, numeric, smallint, tinyint

SET @sql = N'
SELECT StudentID,
    (SELECT MAX(v) FROM (VALUES (' + @cols + N')) AS t(v)) AS MaxScore,
    (SELECT MIN(v) FROM (VALUES (' + @cols + N')) AS t(v)) AS MinScore
FROM dbo.StudentScores;';
EXEC sp_executesql @sql;

最佳實務建議

1. 優先使用 CROSS APPLY + VALUES：語意清晰、效能好、易擴充。
2. 若欄位固定 ≤ 5 個且極度追求效能，可用 CASE 並加註釋。
3. 避免在 WHERE/HAVING 中重複計算，改用 CTE 或 CROSS APPLY 先算好再篩選。
4. 注意 NULL 語意：MAX/MIN 忽略 NULL；若需視 NULL 為最小/最大，請用 COALESCE(v, -2147483648) 等哨兵值。