MySQL在使用GROUP BY DATE（table.timestamp）时填写缺少的日期，而不加入临时表 [英] MySQL to fill in missing dates when using GROUP BY DATE(table.timestamp) without joining on temporary table

问题描述

在阅读了几个相似的Qs /因为我还没有找到我正在寻找的解决方案。我的表数据是GROUP BY DATE（时间戳）并返回一个计数，例如：

after reading through a couple similar Qs/As I haven't quite found the solution I'm looking for. The table data I have is GROUP BY DATE(timestamp) and returning a count, example result:

[timestamp] => 2010-05-12 20:18:36
[count] => 10


[timestamp] => 2010-05-14 10:10:10
[count] => 8

不使用临时表，或日历表有填写这些缺少日期的方法吗？所以使用相同的表格数据将返回：

Without using a temporary table, or calendar table is there a way to fill in those missing dates? so that with the same table data would return:

[timestamp] => 2010-05-12 20:18:36
[count] => 10


/**
 * I would like to have this row added:
 */
[timestamp] => 2010-05-13 00:00:00
[count] => 0

[timestamp] => 2010-05-14 10:10:10
[count] => 8

谢谢！

推荐答案

我没有找到我正在寻找的确切解决方案，但是我认为找到了一种可以满足我需求的方法。我创建了一个流量表，当天的第一个访问者将触发一天中的行插入，现在我可以加入这个表，以获得每日记录。唯一的问题是，如果一天中没有流量，那么日期不会存在于表中。这可以通过运行每日cron来插入新行来解决。

I didn't find the exact solution i was looking for, but I think found a method that works enough for my needs. I created a traffic table where the first visitor of the day will trigger a row insert for the day and now I can join on this table to get a daily record of whatever. The only problem however, is if there is no traffic for a day that date won't exist in the table. This can be solved by running a daily cron to insert a new row.

这篇关于MySQL在使用GROUP BY DATE（table.timestamp）时填写缺少的日期，而不加入临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！