统计最近一周的sql

SELECT DATE_FORMAT(a.create_time, '%m-%d') AS dates,IFNULL(b.sums,0) AS numbers
FROM (
    SELECT CURDATE() AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time
) a LEFT JOIN (
  SELECT DATE(create_time) AS DATETIME, SUM(number) AS sums
  FROM order_detail
  GROUP BY DATE(create_time)
) b ON a.create_time = b.datetime;

思路  先 生成一个临时表  包括 最近一周的每一天   

SELECT CURDATE() AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time

然后 用这个表去连表查询  得到最后结果