生成连续的时间序列

生成当前日期前七天的时间序列(见粗体部分,整体是demo)

select t1.date,ifnull(t2.count,0) count from
(
select date_format(date_add(now(),interval -t.help_topic_id day),’%Y-%m-%d’) date
from mysql.help_topic t where t.help_topic_id<7
) t1
left join
(
select date_format(create_time,’%Y-%m-%d’) date,count(id) count
from zs_referrer_registration_record group by date_format(create_time,’%Y-%m-%d’)
) t2
on t1.date=t2.date
order by t1.date