MySQL-查找MIN，但不为零 [英] MySQL - Find MIN but not zero

问题描述

我正在尝试使用MIN函数在MySQL中获取列的最小值.但是有可能告诉MySQL忽略零值吗?问题是我将0作为默认值而不是NULL列存储为tinyint列.我想要的是获得大于0的最小值.

I am trying to fetch the minimum value of a column in MySQL using the MIN function. But is it possible to tell MySQL to ignore the zero values? Problem is that I am storing 0 as default value instead of NULL for a tinyint column. What I want is to get the minimum value that is greater than 0.

SELECT a.baseloc_id, 
a.baseloc_latitude, 
a.baseloc_longitude, 
a.baseloc_distance, 
MIN(b.basecost_ton2_cost) as minTon2, 
MIN(b.basecost_ton3_cost) as minTon3, 
MIN(b.basecost_ton10_cost) as minTon10 
FROM bbox_logi_base_locations a 
LEFT JOIN bbox_logi_base_cost b 
    USING (baseloc_id) 
GROUP BY a.baseloc_id;

谢谢您的帮助.

对不起，我忘了提及这一点. bbox_logi_base_cost表具有包含碎片值的行.例如，一行可以具有basecost_ton2_cost作为0，而其他列则填充值.而另一行可以具有除0以外的每一列.因此，不能使用WHERE条件过滤任何行.

Sorry that I forgot to mention this. The bbox_logi_base_cost table has rows that contain fragmented values. For example, one row can have basecost_ton2_cost as 0 but other columns filled with values. And the other row can have every column but one as 0. So no row can be filtered using a WHERE condition.

推荐答案

使用此:

MIN(NULLIF(value, 0))

这篇关于MySQL-查找MIN，但不为零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！