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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/NextAction/article/details/80708793

一列多行,很容易得出最大值,但多列多行如何取最大值呢?从下表中取得每行的最大值:

最终实现如下效果:

建表语句:

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;



展开阅读全文

没有更多推荐了,返回首页