如何从mysql中的单行获取最小值 [英] How to get min value from a single row in mysql

问题描述

我想知道如何从单行中获取最小数值，例如:

I would like to know how to get the min numeric value from a single row, for example:

我的桌子:

|col a|col b|col c|some other col|some other col|
-------------------------------------------------
|  13 |  2  |  5  |     0        |       0      |

问题是:如何从col a，col b和col c获得最小值(我想忽略其他列.

The question is: how do I get the min value from col a, col b and col c (I want to ignore the other columns.

我已经尝试过了: 从表WHERE id = 0中选择MIN(col a，col b，col c)

I have try this: SELECT MIN(col a, col b, col c) from table WHERE id=0

但是，这确实行不通.

But surly this doesn't work.

任何帮助将不胜感激.

Any help will be appreciated.

推荐答案

MySQL中正确的函数是least():

The correct function in MySQL is least():

SELECT least(cola, colb, colc) 
FROM table
WHERE id = 0;

非常重要:这假定所有值都不为NULL.如果您具有NULL值，则least()返回NULL.

Very important: This assumes that all values are non-NULL. If you have NULL values, then least() returns NULL.

如果需要考虑到这一点，可以使用coalesce(). . .一种方法是使用最大值:

If you need to take this into account, you can use coalesce() . . . One method is to use a highest value:

SELECT nullif(least(coalesce(cola, 999999), coalesce(colb, 999999), coalesce(colc, 999999)), 999999)
FROM table
WHERE id = 0;

或者，在列中的值上使用coalesce():

Or, use a coalesce() on the values in the columns:

SELECT least(coalesce(cola, colb, colc), coalesce(colb, colc, cola), coalesce(colc, colb, cola))
FROM table
WHERE id = 0;

这篇关于如何从mysql中的单行获取最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！