mysql查询七天数据，没有数据补0

 mysql查询七天数据，没有数据补0

select DATE_FORMAT(a.timeDay,'%m-%d') as time,ifnull(b.price,0) as price
from ( 
	SELECT curdate() as timeDay  
	union all
    SELECT date_sub(curdate(), interval 1 day) as timeDay 
    union all
    SELECT date_sub(curdate(), interval 2 day) as timeDay 
    union all
    SELECT date_sub(curdate(), interval 3 day) as timeDay 
    union all
    SELECT date_sub(curdate(), interval 4 day) as timeDay 
    union all
    SELECT date_sub(curdate(), interval 5 day) as timeDay 
    union all
    SELECT date_sub(curdate(), interval 6 day) as timeDay 
) a left join ( 
	select date(deal_time) as time,sum(if(o.is_pay=1,o.real_price,0)) price 
	from goods_order o 
	where o.`status` = 1
	group by date(o.deal_time) // 这里写你按照七天分组的sql
) b on a.timeDay = b.time order by time

 MySQL查询近12个月数据，无则补0

select v.month,ifnull(b.count,0)  count from (
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`
) v 
left join
(select 
 left(create_time,7) as 'month',count(*) as count
from t_user as a	
		where DATE_FORMAT(a.create_time,'%Y-%m')>
		DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') and del_flag='0'
		GROUP BY month // 这里写按年分组的sql
)b
on v.month = b.month group by v.month

https://blog.csdn.net/kaxialilian/article/details/109116221

https://blog.csdn.net/weixin_44420955/article/details/102729814?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-3.control