WHERE子句MYSQL中的ISNULL(value，0) [英] ISNULL(value, 0) in WHERE clause MYSQL

问题描述

我有这个菜式:

SELECT sc.no, scl.quantite, scl.description, scl.poids, scl.prix, sl_ref.refsl, sl_ref.codetva, sl_ref.tauxtva, sl_ref.compte 
FROM shop_commande 
AS sc, shop_commande_ligne AS scl, selectline_ref AS sl_ref 
WHERE sc.id = scl.shop_commande_id 
AND sl_ref.refshop = ISNULL(scl.shop_article_id, 0) 
AND sc.id NOT IN (SELECT id_command FROM selectline_flag)

有时，在 sl_shop_article_id 中，有一个NULL值.我想要的是将其替换为0，这样子句:

Sometimes, in sl_shop_article_id, there is a NULL value. What I want is to replace it by 0 so the clause:

sl_ref.refshop = scl.shop_article_id

即使 scl.shop_article_id 为NULL，

也可以工作.为此，我尝试使用ISNULL函数，但它使请求出错，并且出现错误:

can work even if scl.shop_article_id is NULL. To do that I tried to use the ISNULL function but it makes the request wrong and I get there error:

1582-调用本机函数"ISNULL"时参数计数不正确

我如何使用它?

推荐答案

我相信您正在尝试使用IFNULL()函数.如果您将ISNULL替换为IFNULL应该可以解决您的查询.

I believe you are trying to use the IFNULL() function. IF you replaced ISNULL with IFNULL that should fix your query.

我建议您更进一步，并使用COALESCE()代替IFNULL()，因为COALESCE()是SQL标准的一部分(而IFNULL()不是).

I suggest you go one step further and use COALESCE() instead of IFNULL(), since COALESCE() is part of the SQL standard (and IFNULL() is not).

SELECT sc.no, scl.quantite, scl.description, scl.poids, 
scl.prix, sl_ref.refsl, sl_ref.codetva, sl_ref.tauxtva, sl_ref.compte 
FROM shop_commande 
AS sc, shop_commande_ligne AS scl, selectline_ref AS sl_ref 
WHERE sc.id = scl.shop_commande_id 
AND sl_ref.refshop = COALESCE(scl.shop_article_id, 0) 
AND sc.id NOT IN (SELECT id_command FROM selectline_flag)

这篇关于WHERE子句MYSQL中的ISNULL(value，0)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！