这段SQL能不能优化一下？

查询过去七天的数据量无数据补0

SELECT a.createdate,IFNULL(b.COUNT,0) AS COUNT
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS createdate
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS createdate

) a LEFT JOIN (
  SELECT DATE(createDate) AS createDate1,COUNT(DISTINCT mId) AS COUNT
  FROM table WHERE id = '000001' 
  GROUP BY DATE(createDate)
) b ON a.createdate = b.createDate1 ORDER BY a.createDate ASC;

后台封装日期列表 循环查询解决

没有必要关联表用case when 或者if代替 a 表

你的sql貌似是错误的吧，即使正确，你的这段ELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS createdate
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 7 DAY) AS createdate
也是消耗时间的，给你提供个思路：可以通过当前时间与当前时间-7作为where条件过滤结果，之后再进行group by按日期分组和排序，在统计数量进行判断。应该一条单表语句就可以实现

哥们，我看了你的答案，我挺头疼的，你知道你这段SQL最大的问题在哪里吗？你循环查询虽然比原来快了，但是也不是最快的，楼上的说法是对的。你最大的问题是b表里条件不全，你最原始的sql中b表就加了一个条件，你还可以加上时间的条件，就是你要查询的7天的时间，作为一个条件加进去，然后再分组排序，这样分组排序的数据会少很多，你在后台生成日期和你在sql生成日期效果是一样的。而且后台封装也是占用资源的，为什么不用sql生成临时的日期数据呢？我给你改一下sql，你参考一下。

select 
    createdate,
    count(distinct mid) count 
from table 
where id = '000001' and 
    date(createdate) between date_sub(curdate(),interval 7 day) and date_sub(curdate(),interval 1 day)
group by 
    createdate
    order by createdate
;