萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 取多列最大值與最小值:VALUES 表值構造器 + UNPIVOT 實戰

場景

表中有多個數值列(如 Score1, Score2, Score3, Score4),需按行求**最大值**與**最小值**。

現代寫法:CROSS APPLY VALUES (SQL Server 2008+)

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 臨時表開銷。

經典寫法: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 會隱式轉換類型,所有參與列**必須同一數據類型**,否則報錯。

舊版本 (2000/2005) 兼容寫法:CASE WHEN

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**。

NULL 處理

  • MAX/MIN **忽略 NULL**。若全 NULL 則結果 NULL。
  • 若需將 NULL 視為 0:ISNULL(Score1,0) 包裹每列,或 COALESCE。

擴展:同時求第 2 大/第 2 小

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
copyright © 萬盛學電腦網 all rights reserved