如何在SQL结果中填充空日期，无论是在MySQL还是Perl端，最直接的方法是什么？

Question

我正在使用类似以下查询从mysql表中构建一个快速的csv：

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

然后在perl中将它们直接转储到一个文件中：

while(my($date,$sum) = $sth->fetchrow) {
    print CSV "$date,$sumn"
}

然而，数据中存在日期间隔：

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

我想要填充数据，用零计数的条目填充缺失的日期，最终得到：

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

我拼凑了一个非常笨拙（几乎肯定有错误）的解决方法，使用了一个月份天数的数组和一些数学运算，但是肯定有更直接的方法，无论是在mysql还是perl方面。

有没有什么天才的想法/为什么我如此愚蠢的建议？

---

最后我选择了使用存储过程生成了一个临时表来处理所需的日期范围，原因有几个：

• 我知道每次都会查找的日期范围
• 不幸的是，这台服务器目前无法安装perl模块，并且它的状态糟糕到没有安装任何类似Date::-y的东西

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

Answer 1

当我需要处理这个问题时，为了填补缺失的日期，我实际上创建了一个参考表，只包含我感兴趣的所有日期，并在日期字段上将数据表连接起来。这很简单粗暴，但它有效。

SELECT DATE(r.date),count(d.date) 
FROM dates AS r 
LEFT JOIN table AS d ON d.date = r.date 
GROUP BY DATE(r.date) 
ORDER BY r.date ASC;

至于输出，我会使用SELECT INTO OUTFILE而不是手动生成CSV。这样我们就不必担心转义特殊字符的问题。

Answer 2

当您在服务器端需要类似的东西时，通常会创建一个包含两个时间点之间所有可能日期的表，然后将此表与查询结果进行左连接。类似于以下内容：

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

在这种特定情况下，最好在客户端进行一些检查，如果当前日期不是前一天的后一天，则添加一些附加字符串。