如果MySQL中的字段为null，则返回0 [英] Return 0 if field is null in MySQL

问题描述

在MySQL中，是否可以将总计"字段设置为零(如果它们为NULL)?

In MySQL, is there a way to set the "total" fields to zero if they are NULL?

这就是我所拥有的:

SELECT uo.order_id, uo.order_total, uo.order_status,
            (SELECT SUM(uop.price * uop.qty) 
             FROM uc_order_products uop 
             WHERE uo.order_id = uop.order_id
            ) AS products_subtotal,
            (SELECT SUM(upr.amount) 
             FROM uc_payment_receipts upr 
             WHERE uo.order_id = upr.order_id
            ) AS payment_received,
            (SELECT SUM(uoli.amount) 
             FROM uc_order_line_items uoli 
             WHERE uo.order_id = uoli.order_id
            ) AS line_item_subtotal
            FROM uc_orders uo
            WHERE uo.order_status NOT IN ("future", "canceled")
            AND uo.uid = 4172;

数据正常，除了NULL字段应为0.

The data comes out fine, except the NULL fields should be 0.

如何在MySQL中为NULL返回0?

How can I return 0 for NULL in MySQL?

推荐答案

使用从文档中:

如果expr1不为NULL，则IFNULL()返回expr1;否则返回expr2. IFNULL()返回数字或字符串值，具体取决于使用它的上下文.

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

这篇关于如果MySQL中的字段为null，则返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！