IFNULL不起作用my_sql

我正在嘗試獲取一個 sql select insede 更新，但始終回傳 null。

UPDATE ps_stock_available sa
SET sa.reserved_quantity = (
    SELECT IFNULL(SUM(od.product_quantity - od.product_quantity_refunded),0) 
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
    os.shipped != 1 AND (
        o.valid = 1 OR (
            os.id_order_state NOT IN ('6,28,59') AND
            os.id_order_state NOT IN ('8')
        )
    ) AND sa.id_product = od.product_id AND
    sa.id_product_attribute = od.product_attribute_id
    GROUP BY od.product_id, od.product_attribute_id
)
WHERE sa.id_shop = 1 AND sa.id_product = 3374;

如果總和為空，則子查詢必須回傳 0。我從 prestashop 類（StockManager.php，方法：updateReservedProductQuantity）獲取此查詢更新。

這是原始代碼：

$updateReservedQuantityQuery = '
            UPDATE {table_prefix}stock_available sa
            SET sa.reserved_quantity = (
                SELECT SUM(od.product_quantity - od.product_quantity_refunded)
                FROM {table_prefix}orders o
                INNER JOIN {table_prefix}order_detail od ON od.id_order = o.id_order
                INNER JOIN {table_prefix}order_state os ON os.id_order_state = o.current_state
                WHERE o.id_shop = :shop_id AND
                os.shipped != 1 AND (
                    o.valid = 1 OR (
                        os.id_order_state != :error_state AND
                        os.id_order_state != :cancellation_state
                    )
                ) AND sa.id_product = od.product_id AND
                sa.id_product_attribute = od.product_attribute_id
                GROUP BY od.product_id, od.product_attribute_id
            )
            WHERE sa.id_shop = :shop_id
        ';

        $strParams = array(
            '{table_prefix}' => _DB_PREFIX_,
            ':shop_id' => (int) $shopId,
            ':error_state' => (int) $errorState,
            ':cancellation_state' => (int) $cancellationState,
        );

        if ($idProduct) {
            $updateReservedQuantityQuery .= ' AND sa.id_product = :product_id';
            $strParams[':product_id'] = (int) $idProduct;
        }

        if ($idOrder) {
            $updateReservedQuantityQuery .= ' AND sa.id_product IN (SELECT product_id FROM {table_prefix}order_detail WHERE id_order = :order_id)';
            $strParams[':order_id'] = (int) $idOrder;
        }

        $updateReservedQuantityQuery = strtr($updateReservedQuantityQuery, $strParams); 

為什么總是回傳 null ？

uj5u.com熱心網友回復：

您需要對外部查詢而不是內部查詢進行 ifnull 檢查，因為內部查詢不能回傳任何行。

您可以在此處使用合并，例如：

UPDATE ps_stock_available sa
SET sa.reserved_quantity = coalesce((
    SELECT SUM(od.product_quantity - od.product_quantity_refunded)
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
    os.shipped != 1 AND (
        o.valid = 1 OR (
            os.id_order_state NOT IN ('6,28,59') AND
            os.id_order_state NOT IN ('8')
        )
    ) AND sa.id_product = od.product_id AND
    sa.id_product_attribute = od.product_attribute_id
    GROUP BY od.product_id, od.product_attribute_id
), 0)
WHERE sa.id_shop = 1 AND sa.id_product = 3374;

標籤：php mysql prestashop

上一篇：Unix時間顯示1h，值為空

下一篇：如何將資料從mysql5.7docker容器匯入mysql8.0docker容器？