按天分组，仍然显示无行的天?

问题描述

我有一个日志表，其中有一个名为logTime的日期字段.我需要显示日期范围内的行数和每天的记录数.问题是我仍想显示没有记录的日期.

I have a log table with a date field called logTime. I need to show the number of rows within a date range and the number of records per day. The issue is that i still want to show days that do not have records.

是否只能使用SQL做到这一点?

Is it possible to do this only with SQL?

示例:

SELECT logTime, COUNT(*) FROM logs WHERE logTime >= '2011-02-01' AND logTime <= '2011-02-04' GROUP BY DATE(logTime);

SELECT logTime, COUNT(*) FROM logs WHERE logTime >= '2011-02-01' AND logTime <= '2011-02-04' GROUP BY DATE(logTime);

它返回如下内容:

+---------------------+----------+
| logTime             | COUNT(*) |
+---------------------+----------+
| 2011-02-01          |        2 |
| 2011-02-02          |        1 |
| 2011-02-04          |        5 |
+---------------------+----------+
3 rows in set (0,00 sec)

我也想展示2011年2月3日这一天.

I would like to show the day 2011-02-03 too.

推荐答案

为完成此任务，您需要有一个表(或派生表)，其中包含您可以随后通过LEFT JOIN加入的日期.

In order to accomplish this, you need to have a table (or derived table) which contains the dates that you can then join from, using a LEFT JOIN.

SQL运用数学集的概念，如果您没有一组数据，则没有任何选择.

SQL operates on the concept of mathematical sets, and if you don't have a set of data, there is nothing to SELECT.

如果您需要更多详细信息，请相应地发表评论.

If you want more details, please comment accordingly.

这篇关于按天分组，仍然显示无行的天?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！