mysql查询30天日期,如何选择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.

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);

This return result like this

Date(a.lastUpdated) count(*)

2016-03-23 1

2016-03-24 14

2016-03-30 65

2016-03-31 1

2016-04-02 1

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.

解决方案

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');

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;