mysql - 将返回值显示为 0 而不是 NULL

Question

我正在尝试从我的数据库中选择数据。如果返回值 = NULL，我想显示 0 而不是 NULL。

我正在尝试使用以下语句:

IF(inv1.id != '', COUNT(inv1.id), '0') AS invoice_count,

当我运行该语句时，我仍然得到NULL。有人知道为什么我仍然得到NULL吗？

这是我的完整声明:

SELECT cus.cus_id, cus.name_cus, cus.contact, cus.place, cus.address, inv1.invoice_count, inv1.invoice_total, cus.status, cus.id FROM cus
LEFT OUTER JOIN (
    SELECT inv1.cus_id,
    IFNULL(COUNT(inv1.id), 0) AS invoice_count,
    IFNULL(CONCAT('€ ', FORMAT(SUM(inv1.total),2)), 0) as invoice_total 
    FROM invoice inv1 
    GROUP BY inv1.cus_id
) inv1 
ON cus.cus_id = inv1.cus_id 
WHERE cus.user_id='2' 

Answer 1

您可以使用IFNULL MySQL的功能。

IFNULL(COUNT(inv1.id), '0') AS invoice_count

更新查询:请使用以下查询

SELECT cus.cus_id, cus.name_cus, cus.contact, cus.place, 
cus.address, ifnull(inv1.invoice_count,0), 
ifnull(inv1.invoice_total,0), 
cus.status, cus.id FROM cus
LEFT OUTER JOIN (
    SELECT inv1.cus_id,
    COUNT(inv1.id) AS invoice_count,
    FORMAT(SUM(inv1.total),2) as invoice_total
   FROM invoice inv1
   GROUP BY inv1.cus_id
) inv1
ON cus.cus_id = inv1.cus_id
WHERE cus.user_id='2';