mysql 中间有空白的日期_如何填补MySQL中的日期空白？

How i can fill date gaps in MySQL? Here is my query:

SELECT DATE(posted_at) AS date,

COUNT(*) AS total,

SUM(attitude = 'positive') AS positive,

SUM(attitude = 'neutral') AS neutral,

SUM(attitude = 'negative') AS negative

FROM `messages`

WHERE (`messages`.brand_id = 1)

AND (`messages`.`spam` = 0

AND `messages`.`duplicate` = 0

AND `messages`.`ignore` = 0)

GROUP BY date ORDER BY date

It returns proper result set - but i want to fill gaps between dates start and end by zeros. How i can do this?

解决方案

You'll need to create a helper table and fill it with all dates from start to end, then just LEFT JOIN with that table:

SELECT d.dt AS date,

COUNT(*) AS total,

SUM(attitude = 'positive') AS positive,

SUM(attitude = 'neutral') AS neutral,

SUM(attitude = 'negative') AS negative

FROM dates d

LEFT JOIN

messages m

ON m.posted_at >= d.dt

AND m.posted_at < d.dt + INTERVAL 1 DAYS

AND spam = 0

AND duplicate = 0

AND ignore = 0

GROUP BY

d.dt

ORDER BY

d.dt

Basically, what you need here is a dummy rowsource.

MySQL is the only major system which lacks a way to generate it.

PostgreSQL implements a special function generate_series to do that, while Oracle and SQL Server can use recursion (CONNECT BY and recursive CTEs, accordingly).