表中有多個數值列(如 Score1, Score2, Score3, Score4),需按行求**最大值**與**最小值**。
SELECT
t.ID,
t.Name,
ca.MaxVal,
ca.MinVal
FROM Scores t
CROSS APPLY (
SELECT MAX(v) AS MaxVal, MIN(v) AS MinVal
FROM (VALUES (t.Score1), (t.Score2), (t.Score3), (t.Score4)) AS v(v)
) ca
優勢:可讀性強、易擴展列、優化器能展開為標量聚合、無需 UNPIVOT 臨時表開銷。
SELECT ID, Name, MAX(Val) AS MaxVal, MIN(Val) AS MinVal
FROM Scores
UNPIVOT (Val FOR Col IN (Score1, Score2, Score3, Score4)) up
GROUP BY ID, Name
注意:UNPIVOT 會隱式轉換類型,所有參與列**必須同一數據類型**,否則報錯。
SELECT
ID, Name,
CASE WHEN Score1 >= Score2 AND Score1 >= Score3 AND Score1 >= Score4 THEN Score1
WHEN Score2 >= Score1 AND Score2 >= Score3 AND Score2 >= Score4 THEN Score2
WHEN Score3 >= Score1 AND Score3 >= Score2 AND Score3 >= Score4 THEN Score3
ELSE Score4 END AS MaxVal,
CASE WHEN Score1 <= Score2 AND Score1 <= Score3 AND Score1 <= Score4 THEN Score1
WHEN Score2 <= Score1 AND Score2 <= Score3 AND Score2 <= Score4 THEN Score2
WHEN Score3 <= Score1 AND Score3 <= Score2 AND Score3 <= Score4 THEN Score3
ELSE Score4 END AS MinVal
FROM Scores
維護性極差,列一多極其痛苦,**強烈建議升級後改用 CROSS APPLY VALUES**。
MAX/MIN **忽略 NULL**。若全 NULL 則結果 NULL。ISNULL(Score1,0) 包裹每列,或 COALESCE。CROSS APPLY (
SELECT
MAX(v) AS MaxVal,
MIN(v) AS MinVal,
MAX(CASE WHEN v < (SELECT MAX(v) FROM (VALUES (t.Score1),(t.Score2),(t.Score3),(t.Score4)) v(v)) THEN v END) AS SecondMax,
MIN(CASE WHEN v > (SELECT MIN(v) FROM (VALUES (t.Score1),(t.Score2),(t.Score3),(t.Score4)) v(v)) THEN v END) AS SecondMin
FROM (VALUES (t.Score1), (t.Score2), (t.Score3), (t.Score4)) AS v(v)
) ca