业务需求:
查询一周每天的数据,没有数据的显示为0
SELECT
weekList.days,
( SELECT count(*) FROM **XXX** WHERE weekList.days = date(create_time) AND company_id = #{companyId} ) AS count
FROM
(
SELECT
date_format( @cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ), '%Y-%m-%d' ) AS days
FROM
( SELECT @cdate := DATE_ADD( curdate(), INTERVAL + 1 DAY ) FROM **XXX**) t0
LIMIT 7
) AS weekList
XXX为表名
create_time :表中的时间字段
AND company_id = #{companyId}:可以添加其他条件筛选
注意:该表的数据至少要有七条