时间戳

首页 > 解决方案 > Mysql填补日期空白

mysql - Mysql填补日期空白

问题描述

我正在尝试从 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 请求有什么问题？

标签： mysqlsqlleft-join

解决方案

似乎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小提琴链接：http ://sqlfiddle.com/#!9/f151a8e/4

推荐阅读