MySQL 查询

Question

我正在尝试创建一个查询，该查询将检查过去 7 天我的数据库中有多少条目，它有效，但它只返回记录存在的日期。我希望它在没有数据的日子里返回 0，因为我正在生成 7 天的报告。

感谢收到的任何帮助!

SELECT DATE(created_date) AS date, 
    (SELECT COUNT(id) 
    FROM records 
    WHERE DATE(created_date) = date AND method = 'tw') AS total_records 
FROM records 
WHERE created_date > DATE_SUB(NOW(), INTERVAL 7 day) 
GROUP BY date

Answer 1

SELECT DATE(NOW() + INTERVAL num.i DAY) AS date, 
       COUNT(r.id) AS total_records 
FROM 
        ( SELECT 0 AS i UNION ALL               -- where 0 means today
          SELECT -1 UNION ALL                   -- and -1 is yesterday
          ...
          SELECT -6
        ) AS num
    LEFT JOIN
        records AS r
            ON  r.created_date >= DATE(NOW() + INTERVAL num.i DAY)
            AND r.created_date < DATE(NOW() + INTERVAL 1+num.i DAY)
            AND r.method = 'tw'
GROUP BY num.i ;

(method, created_date) 上的索引将有助于提高效率。