用SQL从多行数据里选取最大值或者最小值,我们可以通过GROUP BY 子句对合适的列进行聚合操作,然后使用MAX或者MIN聚合函数就可以求出,但该怎么从多列数据中选取最大值呢?
案例
建立一个表Greatests:
要求求出每一行的最大值
代码实现
在操作之前,我们可以先考虑下如何先实现两列的对比。
SELECT `key`,
CASE WHEN X<Y THEN Y ELSE X
END AS `greatest`
FROM Greatests
GROUP BY `key`
输出结果:
这里使用case表达式来对x和y列进行判断,返回最大的值。扩展到三列时,需要做的就是在里面在嵌套一个case表达式,和z列进行比较:
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
GROUP BY `key`
输出结果:
注意,在case表达式中,最后的else建议要写上,否则默认的是null,在有些情况下输出的时候不会报错,但会造成结果上的错误!
拓展
我们既然实现了三列的比较,那么根据这个思路其实也可以实现四列或者五列的查找,但是这样一来case表达式的嵌套就增多,影响代码的可读性,这时我们可以进行行列转换,通MAX进行求解:
SELECT `key`,MAX(col) `greatest`
FROM(SELECT `key`,X col FROM Greatests UNION ALL
SELECT `key`,Y col FROM Greatests UNION ALL
SELECT `key`,z col FROM Greatests ) AS tmp
GROUP BY `key`
使用这一方法,我们仍然可以得出结果:
greatest和least
其实在Oracle和MySQL数据库中加入了greatest和least两个函数来帮助我们进行上述步骤的操作
greatest是求最大值,least是求最小值:
#最大值
SELECT `key`,
GREATEST(GREATEST(X,Y),z) AS `greatest`
FROM Greatests GROUP BY `key`
#最小值
SELECT `key`,LEAST(LEAST(X,Y),z) AS `greatest` FROM Greatests GROUP BY `key`