在查询日期时将日期中没有的值补齐

1.先查询出你想要查询的日期范围（示例为15天）

SELECT
			@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
		FROM
			(
				SELECT
					@cdate := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL + 1 DAY)
				FROM
					pay_order
				LIMIT 15
			) t0

如图所示查询到的结果为当前时间前15天的天数

2.查询自己数据库的日期和数据

SELECT sum(TOTAL_AMOUNT) totalMoney,DATE_FORMAT(UPDATE_TIME,'%m-%d-%Y') day
   FROM pay_order 
   WHERE  TRADE_STATE = 2 AND(TO_DAYS(NOW( ) ) - TO_DAYS(UPDATE_TIME) <= 15 )
   GROUP BY DATE_FORMAT(UPDATE_TIME,'%m-%d-%Y')  DESC

 这是查询自己数据库中进15天的记录

3.用左连接将两条数据进行合并

SELECT 
		t1.DAY,
		IFNULL(t2.totalMoney, 0) AS totalMoney
FROM
	(
		SELECT
			@cdate := DATE_ADD(@cdate, INTERVAL - 1 DAY) DAY
		FROM
			(
				SELECT
					@cdate := DATE_ADD(DATE_FORMAT(NOW(),'%Y-%m-%d'), INTERVAL + 1 DAY)
				FROM
					pay_order
			) t0
		LIMIT 15
	) t1
LEFT JOIN (
	SELECT sum(TOTAL_AMOUNT) totalMoney,DATE_FORMAT(UPDATE_TIME,'%Y-%m-%d') time
   FROM pay_order 
   WHERE  TRADE_STATE = 2 AND(TO_DAYS(NOW( ) ) - TO_DAYS(UPDATE_TIME) <= 15 )
   GROUP BY DATE_FORMAT(UPDATE_TIME,'%Y-%m-%d')  DESC
) t2 
ON t1. DAY = t2. time
ORDER BY
	DAY DESC

 这是合并以后的结果