Returns the first non-NULL
value in the list, or NULL
if there are no non-NULL
values.
例子1:基础使用
mysql> SELECT COALESCE(NULL,1);
-> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
-> NULL
例子2:查询商品库存及价格
SELECT
spu.`name`,
sku.price,
COALESCE ( stock.stock, 0 ) stock,
COALESCE ( stock.surplus_stock, 0 ) surplus_stock
FROM
product_spu spu
JOIN product_sku sku ON sku.spu_id = spu.id
LEFT JOIN product_stock stock ON stock.sku_id = sku.id
WHERE
NAME LIKE "kayla%"
AND spu.`status` = 1
AND sku.dr = 0
ORDER BY
spu.id
例子3:查询商品总库存
SELECT
spu.`name`,
COALESCE (sum(stock.stock), 0 ) total_stock
FROM
product_spu spu
JOIN product_sku sku ON sku.spu_id = spu.id
LEFT JOIN product_stock stock ON stock.sku_id = sku.id
WHERE
NAME LIKE "kayla%"
AND spu.`status` = 1
AND sku.dr = 0
GROUP BY spu.`name`
ORDER BY spu.`name`