这段SQL能不能优化一下？

Question

查询过去七天的数据量无数据补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;

Answer 1

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