如何在没有count 0的情况下在mysql查询中实现最少？

I want to implement least in mysql query but least should not be count 0 how can i do that?

我想在mysql查询中实现最少,但最少不应该是0,我该怎么做?

this is my query

这是我的疑问

    LEAST(price, price_double, price_triple, price_quad) AS minvalue

it is counting 0 right now but i dont want to count 0 and search the result with out having zero

它现在正在计数0,但我不想计数0并搜索结果没有

For example

   price            100
   price_double     75
   price_tripple    50
   price_quad       0

the minvalue will be 50 not 0, but right now it is including 0.

minvalue将是50而不是0,但现在它包括0。

Please help me to do that, i m in very trouble, i search a lot but didn't get any success, thanks a million ton thanks in advance.

请帮助我这样做,我很麻烦,我搜索了很多但没有取得任何成功,感谢提前一百万吨感谢。

3 个解决方案

#1

You can use case in the least() function to do this. Unfortunately, least() returns NULL if any arguments are NULL in the more recent versions of MySQL. So, this uses a dummy value that is big:

您可以在least()函数中使用case来执行此操作。不幸的是,如果MySQL的更新版本中的任何参数为NULL,则least()返回NULL。所以,这使用了一个很大的虚拟值:

least(case when price > 0 then price else 999999 end,
      case when price_double > 0 then price_double else 999999 end,
      case when price_triple > 0 then price_triple else 999999  end,
      case when price_quad > 0 then price_quad else 999999 end
     )

#2

Use where clause

使用where子句

 SELECT LEAST(price, price_double, price_triple, price_quad)
 FROM ....
 WHERE LEAST(price, price_double, price_triple, price_quad) > 0

#3

This is the same like Gordon Linoff's solution only a little shorter:

这与Gordon Linoff的解决方案相同,只是缩短了一点:

LEAST
(
    IF(price > 0, price, 999999),
    IF(price_double > 0, price_double, 999999),
    IF(price_triple > 0, price_triple, 999999),
    IF(price_quad > 0, price_quad, 999999)
)

#1