問題描述

我正在從一個 mysql 表構建一個快速的 csv，查詢如下:

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;

然后將它們轉儲到 perl 文件中:

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 | 

我用每月的天數和一些數學計算了一個非常尷尬(而且幾乎肯定有問題)的解決方法，但是在 mysql 或 perl 方面必須有更簡單的方法.

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:

• 我知道每次要查找的日期范圍
• 不幸的是，有問題的服務器不是我可以在 atm 上安裝 perl 模塊的服務器，而且它的狀態已經陳舊到沒有遠程安裝任何東西 Date::-y

perl Date/DateTime 迭代答案也很好，我希望我可以選擇多個答案！

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;
end procedure

在這種特殊情況下，最好在客戶端進行一些檢查，如果當前日期不是 previos+1，則添加一些附加字符串.

In this particular case it would be better to put a little check on the client side, if current date is not previos+1, put some addition strings.

這篇關于在 sql 結果中填充空日期的最直接方法是什么(在 mysql 或 perl 端)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！