COALESCE函数使用

最新推荐文章于 2025-06-11 14:59:15 发布

原创最新推荐文章于 2025-06-11 14:59:15 发布 · 559 阅读

0 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了SQL中COALESCE函数的功能与用法，通过三个实例展示了如何使用COALESCE函数处理NULL值，包括基础使用、查询商品库存及价格、计算商品总库存等场景，为数据库操作提供实用技巧。

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`