Mysql 填补日期空白

Question

我正在尝试从 dataTable 中获取过去 7 天的值，如果没有特定日期的数据，则应在输出中填写零:

SELECT calendar.dt, COALESCE(SUM(weight*price),0) AS amount
FROM
    (SELECT CURRENT_DATE AS dt
       UNION ALL SELECT CURRENT_DATE - INTERVAL 1 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 2 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 3 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 4 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 5 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 6 DAY) calendar
LEFT JOIN dataTable ON calendar.dt = DATE(dataTable.sold_at);

上述请求仅返回一条记录:

请告知SQL请求有什么问题？

Answer 1

似乎缺少group by。

SELECT calendar.dt, COALESCE(SUM(weight*price),0) AS amount
FROM
    (SELECT CURRENT_DATE AS dt
       UNION ALL SELECT CURRENT_DATE - INTERVAL 1 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 2 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 3 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 4 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 5 DAY
       UNION ALL SELECT CURRENT_DATE - INTERVAL 6 DAY) calendar
LEFT JOIN dataTable ON calendar.dt = DATE(dataTable.sold_at)
Group by calendar.dt
;

SQL fiddle 链接:http://sqlfiddle.com/#!9/f151a8e/4