IFNULL 在my_sql不起作用

Question

我试图在更新中获取sql select，但总是返回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;

如果总和为null，则子查询必须返回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？

Answer 1

您需要对外部查询进行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;