如何选择MySQL中最近30天的日期，即使MySQL中不存在日期? [英] How to select last 30 days dates in MySQL even the dates are not present in mysql?

问题描述

在这种情况下，我需要获取过去30天的数据并获取所有日期，即使指定日期的mysql中没有数据.

I have a situation here where i need to grab data from past 30 days and get all the dates even if the data is not present in mysql for given dates.

我当前的查询是这样的. lastUpdated是一个时间戳列

my current query is something like this. lastUpdated is a timestamp column

SELECT Date(a.lastUpdated), count(*)
FROM table1 a
LEFT JOIN table2 b on (a.pgid = b.prod_id)
WHERE Date(lastUpdated) BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()
GROUP BY Date(a.lastUpdated);

这样的返回结果

Date(a.lastUpdated)   count(*)
2016-03-23              1
2016-03-24             14
2016-03-30             65
2016-03-31              1
2016-04-02              1

我的问题是，即使数据不在mysql中，是否也可能列出所有日期.或任何其他解决方法.我正在使用PHP 7.0.

My question is if its possible to list out all the dates even if the data is not in the mysql. Or any other workaround. I am using PHP 7.0.

我环顾四周，但没有发现与我的要求有关的任何东西.

I looked around SO but didn't find anything related to my requirements.

推荐答案

假设一个简化的示例表table1具有以下内容

Assuming a simplified example table table1 with the following contents

CREATE TABLE table1 (lastUpdated datetime);
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-23');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-24');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-30');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-31');
INSERT INTO table1 (lastUpdated) VALUES ('2016-04-02');
INSERT INTO table1 (lastUpdated) VALUES ('2016-03-31');

然后下面的MySQL语句将返回过去30天内的所有日期，计数为0，其中在table1中找不到任何条目:

Then the following MySQL statement will return all dates in the past 30 days, with a count of 0 where no entry is found in table1:

SELECT lastUpdated, count(*)-1 FROM (
    SELECT date(lastUpdated) as lastUpdated FROM table1 as t1
    UNION ALL 
    SELECT curdate() - interval a day AS lastUpdated FROM (
        select 0 as a union select 1 union select 2 union select 3 union
        select 4 union select 5 union select 6 union select 7 union
        select 8 union select 9 union select 10 union select 11 union
        select 12 union select 13 union select 14 union select 15 union 
        select 16 union select 17 union select 18 union select 19 union
        select 20 union select 21 union select 22 union select 23 union
        select 24 union select 25 union select 26 union select 27 union
        select 28 union select 29
    ) as t2
) as t3 GROUP BY lastUpdated;

这篇关于如何选择MySQL中最近30天的日期，即使MySQL中不存在日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！