mysql日期序列填充,在sql结果中填充空日期最简单的方法是什么（在mysql或perl end）？...-CSDN博客

I'm building a quick csv from a mysql table with a query like:

select DATE(date),count(date) from table group by DATE(date) order by date asc;

and just dumping them to a file in perl over a:

while(my($date,$sum) = $sth->fetchrow) {

print CSV "$date,$sum\n"

}

There are date gaps in the data, though:

| 2008-08-05 | 4 |

| 2008-08-07 | 23 |

I would like to pad the data to fill in the missing days with zero-count entries to end up with:

| 2008-08-05 | 4 |

| 2008-08-06 | 0 |

| 2008-08-07 | 23 |

I slapped together a really awkward (and almost certainly buggy) workaround with an array of days-per-month and some math, but there has to be something more straightforward either on the mysql or perl side.

Any genius ideas/slaps in the face for why me am being so dumb?

I ended up going with a stored procedure which generated a temp table for the date range in question for a couple of reasons:

I know the date range I'll be looking for every time

The server in question unfortunately was not one that I can install perl modules on atm, and the state of it was decrepit enough that it didn't have anything remotely Date::-y installed

The perl Date/DateTime-iterating answers were also very good, I wish I could select multiple answers!

解决方案

When you need something like that on server side, you usually create a table which contains all possible dates between two points in time, and then left join this table with query results. Something like this:

create procedure sp1(d1 date, d2 date)

declare d datetime;

create temporary table foo (d date not null);

set d = d1

while d <= d2 do

insert into foo (d) values (d)

set d = date_add(d, interval 1 day)

end while

select foo.d, count(date)

from foo left join table on foo.d = table.date

group by foo.d order by foo.d asc;

drop temporary table foo;