【SQL练习题】1. 多列数据求最大值

一列多行，很容易得出最大值，但多列多行如何取最大值呢？从下表中取得每行的最大值：

最终实现如下效果：

建表语句：

CREATE TABLE greatests (
  key varchar(255),
  x int(5),
  y int(5),
  z int(5)
);

INSERT INTO greatests VALUES ('A', 1, 2, 3);
INSERT INTO greatests VALUES ('B', 5, 5, 2);
INSERT INTO greatests VALUES ('C', 4, 7, 1);
INSERT INTO greatests VALUES ('D', 3, 3, 8);

分析：

可以先行列转换，然后取每列的最大值

select key,max(col) as greatests from 
(select key,x as col from greatests
union all
select key,y as col from greatests
union all
select key,z as col from greatests)tmp
group by key
order by key;

或者使用greatest函数：

select key,GREATEST(GREATEST(x,y),z) as greatests from greatests;