mySql中union all 及max，ifnull用法

最新推荐文章于 2024-04-02 05:00:00 发布

DreamsComeTrue1

最新推荐文章于 2024-04-02 05:00:00 发布

阅读量1.4k

点赞数

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

本文链接：https://blog.csdn.net/csdnlaiyanqi/article/details/121407261

版权

本文介绍了MySQL中UNION ALL结合MAX和IFNULL的使用方法。重点包括：1) 使用UNION ALL连接查询时，字段数量必须一致，空值可用''填充；2) 利用IFNULL函数处理NULL值，例如IFNULL(d.money, 0)作为totalMoney；3) 当需要合并多条记录为一条时，需对所有字段应用MAX函数，如max(d.orderNum)，以避免字段值为空的情况。" 113578766,10547992,Vue3 watch与watchEffect深度解析,"['Vue.js', '前端开发', 'Vue3 API']

摘要由CSDN通过智能技术生成

mySql中union all 及max，ifnull用法

select
	ifnull(max(d.orderNum),0) totalNum,
	ifnull(max(d.money),0) totalMoney,
	ifnull(max(d.unpayOrderNum),0) unpaidNum,
	ifnull(max(d.unpayMoney),0) unpaidMoney,
	ifnull(max(d.payOrderNum),0) paidNum,
	ifnull(max(d.PayMoney),0) paidMoney from 
		(SELECT
			count(1) orderNum,
			sum(money) money,
			'' unpayOrderNum,
			'' unpayMoney,
			'' payOrderNum,
			'' PayMoney
		from 
			os_order_form
		where del_flag = 0
		UNION ALL
		SELECT
			'' orderNum,
			'' money,
			count(1) unpayOrderNum,
			sum(money) unpayMoney,
			'' payOrderNum,
			'' PayMoney
		from 
			os_order_form
		where 
			del_flag = 0 
			and 
			pay_time is null
		UNION ALL
	 SELECT
			'' orderNum,
			'' money,
			'' unpayOrderNum,
			'' unpayMoney,
			count(1) payOrderNum,
			sum(money) PayMoney
		from 
			os_order_form