在sql结果中填充空日期最简单的方法是什么（在mysql或perl end）？ [英] What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

问题描述

 选择DATE（date），count （date）from table group by DATE（date）order by date asc; 
  

，并将它们转储到perl中的文件中：

  while（my（$ date，$ sum）= $ sth-> fetchrow）{
 print CSV$ date，$ sum\\\
 
} 
  

但是数据中有日期差距：

  | 2008-08-05 | 4 | 
 | 2008-08-07 | 23 | 
  

我想填充数据以填充零计数条目的缺失日期与：

  | 2008-08-05 | 4 | 
 | 2008-08-06 | 0 | 
 | 2008-08-07 | 23 | 
  

我在一起打了一个非常尴尬（几乎肯定是buggy）月和一些数学，但是有一些更直接的东西在mysql或perl一边。

任何天才的想法/拍板在脸上为什么我这么蠢？

---

我最后使用一个存储过程为相应的日期范围生成一个临时表，原因如下：

• 我知道每次都会查找的日期范围


• 有问题的服务器不是可以安装perl模块的服务器on atm，并且它的状态是破旧的，它没有任何远程的日期Date ::安装

日期/日期时间迭代的答案也很好，我希望我可以选择多个答案！

解决方案

在服务器端，通常创建一个表，其中包含两个时间点之间的所有可能的日期，然后将此表与查询结果连接。类似这样：

  create procedure sp1（d1 date， d2 date）
 declare d datetime; 
 
创建临时表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日期）
 end while 
 
选择foo.d，count（date）
来自foo left join table on foo.d = table.date 
 group by foo。 d order by foo.d asc; 
 
删除临时表foo; 
结束过程
  

在这种特殊情况下，客户端，如果当前日期不是previos + 1，请添加一些添加字符串。

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

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 end）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！