COALESCE函数使用

COALESCE(value,...)

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`