SQL 使用CASE表达式求三列中的最大值

--求x、y和z三者中的最大值

SELECT num,
    CASE WHEN CASE WHEN x < y THEN y ELSE X END < z
        THEN z
           ELSE CASE WHEN x < y THEN y ELSE x END
        END AS greatest
    FROM Greatests;

如果是Oracle和MySQL数据库，那么我们可以使用下面的简便方法求解。

SELECT num, GREATEST(x,y,z) AS greatest 
    FROM Greatests;

如果不止三列，存在4列、5列时，可以使用行转列，再通过MAX函数求解。

SELECT num, MAX(col) AS greatest
    FROM (SELECT num, x AS col FROM Greatests
            UNION ALL
          SELECT num, y AS col FROM Greatests
            UNION ALL
          SELECT num, z AS col FROM Greatests) TMP
    GROUP BY num;