mysql - mysql 计数的年、月分组结果不正确

Question

我遇到的查询计数不正确的问题如下:

mysql> SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%M') AS month,
YEAR(FROM_UNIXTIME(timestamp)) AS year, count(*) AS count FROM table1 WHERE
result LIKE '%created%' GROUP BY YEAR(FROM_UNIXTIME(timestamp)),
MONTH(FROM_UNIXTIME(timestamp)) DESC;

+-----------+------+-------+
| month     | year | count |
+-----------+------+-------+
| September | 2011 |     1 |
| December  | 2013 |   393 |
| November  | 2013 |    70 |
| September | 2014 |   233 |
| August    | 2014 |   739 |
| July      | 2014 |   691 |
| June      | 2014 |   618 |
| May       | 2014 |   120 |
| March     | 2014 |   272 |
| February  | 2014 |   528 |
| January   | 2014 |   607 |
+-----------+------+-------+
11 rows in set (0.40 sec)

但是，当我使用另一种语法检查我的工作时，我得到了不同的结果:

mysql> select count(*) from table1 where result like '%created%' and
timestamp >= unix_timestamp('2014-07-01') and timestamp <= unix_timestamp('2014-07-31');
+----------+
| count(*) |
+----------+
|      662 |
+----------+
1 row in set (0.39 sec)

语法有什么问题？

Answer 1

在第二个查询中使用 < unix_timestamp('2014-08-01')。我认为您过滤掉了 2014-07-31 00:00:00 am 之后发生的所有事情。那天还有 24 小时不应该被过滤。