Mysql 求和字段有null得到结果为null的解决方法

最新推荐文章于 2023-11-11 20:12:38 发布

pandasen

最新推荐文章于 2023-11-11 20:12:38 发布

阅读量2.3k

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/pandasen/article/details/125884902

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在数据查询时，多个字段相加的情况下，当有一个字段为null，最终求和的结果会变成null，可以使用mysql自带的函数

IFNULL(exp1,exp2)，exp1为字段名，exp2为字段为null时的赋值

SELECT
	dealer.*, dealer.money + dealer.freeze_money + dealer.total_money AS total_amount,
	IFNULL(
		sum(firstOrder.order_price),
		0
	) + IFNULL(
		sum(secondOrder.order_price),
		0
	) + IFNULL(
		sum(thirdOrder.order_price),
		0
	) AS total_order_amount,
	count(firstOrder.order_id) + count(secondOrder.order_id) + count(thirdOrder.order_id) AS total_order_nums
FROM
	`5f_dealer_user` `dealer`
LEFT JOIN `5f_dealer_order` `firstOrder` ON `firstOrder`.`first_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `secondOrder` ON `secondOrder`.`second_user_id` = `dealer`.`user_id`
LEFT JOIN `5f_dealer_order` `thirdOrder` ON `thirdOrder`.`third_user_id` = `dealer`.`user_id`
WHERE
	(
		`dealer`.`wxapp_id` = 10001
		AND `dealer`.`wxapp_id` = 10001
	)
AND `dealer`.`is_delete` = 0
GROUP BY
	`dealer`.`user_id`
ORDER BY
	`total_amount` DESC,
	`dealer`.`create_time` DESC