如何在SQL的特定行中找到最少的非空列? [英] How to find least non-null column in one particular row in SQL?

问题描述

我试图在同一张表的一行的两列中找到最低的数字，但要注意的是，在特定行中，其中一列可能为null.如果其中一列为空，则我希望为该行返回另一列中的值，因为在这种情况下，这是最低的非空列.如果我在MySQL 5.1中使用minimum()函数:

I am trying to find the lowest number in two columns of a row in the same table, with the caveat that one of the columns may be null in a particular row. If one of the columns is null, I want the value in the other column returned for that row, as that is the lowest non-null column in this case. If I use the least() function in MySQL 5.1:

select least(1,null)

这返回null，这不是我想要的.在这种情况下，我需要查询返回1.

This returns null, which is not what I want. I need the query to return 1 in this case.

通过此查询，我通常可以获得所需的结果:

I've been able to get the result I want in general with this query:

select least(coalesce(col1, col2)) , coalesce(col2,col1))

只要col1和col2都不为空，则每个结盟语句都会返回一个数字，并且minimum()会处理找到最小值的情况.

As long as col1 and col2 are both not null each coalesce statement will return a number, and the least() handles finding the lowest.

是否有更简单/更快的方法来做到这一点?在这种情况下，我使用的是MySQL，但欢迎使用一般的解决方案.

Is there a simpler/faster way to do this? I'm using MySQL in this instance but general solutions are welcomed.

推荐答案

不幸的是(对于您的情况)在MySQL 5.0.13中更改了LEAST的行为(

Unfortunately (for your case) behaviour of LEAST was changed in MySQL 5.0.13 (http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_least) - it used to return NULL only if all arguments are NULL.

甚至将此更改报告为一个错误: http://bugs.mysql.com /bug.php?id=15610 但是该修复程序仅适用于MySQL文档，解释了新的行为和兼容性中断.

This change was even reported as a bug: http://bugs.mysql.com/bug.php?id=15610 But the fix was only to MySQL documentation, explaining new behaviour and compatibility break.

您的解决方案是建议的解决方法之一.另一个可以使用IF运算符:

Your solution was one of the recommended workarounds. Another can be using IF operator:

SELECT IF(Col1 IS NULL OR Col2 IS NULL, COALESCE(Col1, Col2), LEAST(Col1,Col2))

这篇关于如何在SQL的特定行中找到最少的非空列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！