MySQL - 查找 MIN 但不为零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2099720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - Find MIN but not zero
提问by Nirmal
I am trying to fetch the minimum value of a column in MySQL using the MINfunction. But is it possible to tell MySQL to ignore the zero values? Problem is that I am storing 0as default value instead of NULLfor a tinyintcolumn. What I want is to get the minimum value that is greater than 0.
我正在尝试使用该MIN函数获取 MySQL 中列的最小值。但是是否可以告诉 MySQL 忽略零值？问题是，我保存0为默认值，而不是NULL一tinyint列。我想要的是获得大于 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;
Thank you for any help.
感谢您的任何帮助。
EDIT 01:
编辑 01：
Sorry that I forgot to mention this. The bbox_logi_base_costtable has rows that contain fragmented values. For example, one row can have basecost_ton2_costas 0but 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 WHEREcondition.
对不起，我忘了提到这一点。该bbox_logi_base_cost表具有包含碎片值的行。例如，一个行可以有basecost_ton2_cost作为0，但其他列填入值。另一行可以将每一列都作为0. 因此，不能使用WHERE条件过滤任何行。
回答by David M
Use this:
用这个：
MIN(NULLIF(value, 0))
回答by Ninet9
The correct answer for you is:
你的正确答案是：
IFNULL(MIN(NULLIF(value, 0)), 0)
While ISNULLnot working.
虽然ISNULL不工作。
