mysql查询近七天、近一年数据，没有数据补0

Mysql笔记:（mysql查询七天数据，没有数据补0）

一、近七天sql语句：

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) 
) b on a.timeDay = b.time order by time

1. 注解：
   select
   DATE_FORMAT(a.timeDay,’%m-%d’) as time //可对展示时间格式化
   ,ifnull(b.price,0) as price //查询无数据补0，不加ifnull则时间为空
   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 //已支付则记录数据，未支付记0
   from goods_order o where o.status = 1
   group by date(o.deal_time)
   ) b //然后左连需要查询的表
   on a.timeDay = b.time
   order by time //最后对时间进行顺序排序

二、近一年sql语句：

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(a.account_time,7) AS 'month'
		,0-SUM(bill_amount) AS COUNT 
		FROM pck_bill_detail AS a WHERE DATE_FORMAT(a.`account_time`,'%Y-%m')>
        DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH),'%Y-%m') 
		  AND a.del_flag = 0
        GROUP BY MONTH) AS b
      ON v.month = b.month GROUP BY v.month