sql - 如果字段在 MySQL 中为空，则返回 0

Question

在 MySQL 中，如果“总计”字段为 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。

如何在 MySQL 中为 NULL 返回 0？

Answer 1

使用 IFNULL :

IFNULL(expr1, 0)

来自文档:

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.