求多列的最大值,最小值greatest(expr_1,expr_2,…expr_n) least(expr_1,expr_2,…expr_n)
表: greatests
KEY X Y Z
A 2 4 7
B 4 5 1
需得到如下结果:
KEY MAX
A 7
B 5
解决方式:
第一种:A
select key,
case when (case when x>y then y else x end )y then y else x end end
from greatests ;
第二种:
select key,max(col)
from (
select key ,X as col from greatests
union
select key ,Y as col from greatests
union
select key ,Z as col from greatests
) A
group by key;
第三中:(适用于Oracle与mysql数据库)
SELECT KEY,
greatest (X, Y, Z) max,
least(X, Y, Z) min
FROM tb;
当有一个表达式是null时,结果都为null。