最大值判断:
问题描述:从多列数据里选出最大值
![9e535a9674c026b451777e35fa059b3e.png](https://i-blog.csdnimg.cn/blog_migrate/5e885bfaa5f486ae4fc6b88bc7940209.png)
两列最大值判断:
SELECT key,
CASE WHEN x < y THEN y
ELSE x END AS greatest
FROM Greatests;
三列最大值判断:
SELECT key,
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
也许这样看你会更明白
SELECT key,
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
讲解:
- 判断z是否为最大值
- 1 生成x或y中的最大值与z比较
- 若z是否为最大值,则返回z,否则:
- 判断x或y是否为最大值
多列最大值判断:
将数据表转化为行形式,利用union all连接。如果增加新的列,则继续添加相关语句在from后面的语句中。
SELECT key, MAX(col) AS greatest
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;