MySQL：按日期分组统计出现次数

Question

我想这是一个经常出现的问题，但我没有找到正确的主题来指引我方向。

我有一个名为“chat”的表格，它看起来像这样：

+---------+----------------+---------------------+-----------------+ | id(int) | author(string) | date(datetime) | message(string) | +---------+----------------+---------------------+-----------------+ | 1 | John | 2016-01-01 17:18:00 | I | | 2 | Mary | 2016-01-01 14:22:00 | Just | | 3 | John | 2016-01-01 09:02:00 | Want | | 4 | John | 2016-01-02 17:18:00 | To | | 5 | Mary | 2016-01-03 18:26:00 | Say | | 6 | John | 2016-01-03 10:42:00 | Hello | +---------+----------------+---------------------+-----------------+

我想得到的结果如下：

+------------+------+------+ | day | Mary | John | +------------+------+------+ | 2016-01-01 | 1 | 2 | | 2016-01-02 | 0 | 1 | | 2016-01-03 | 1 | 1 | +------------+------+------+

我是否必须在COUNT语句中使用子查询？

到目前为止，我想到的是：

SELECT DATE(date) as day,
(SELECT COUNT(id) FROM chat WHERE author = 'Mary') AS 'Mary'
(SELECT COUNT(id) FROM chat WHERE author = 'John') AS 'John'
FROM chat
GROUP BY day
ORDER BY day ASC

但是这会在每一行中给我每个作者的总消息计数：

+------------+------+------+
|    day     | Mary | John |
+------------+------+------+
| 2016-01-01 |    2 |    4 |
| 2016-01-02 |    2 |    4 |
| 2016-01-03 |    2 |    4 |
+------------+------+------+

- Pierre Roudaut

Answer 1

只需使用条件聚合：

SELECT DATE(date) as day,
       SUM(author = 'Mary') AS Mary,
       SUM(author = 'John') AS John
FROM chat
GROUP BY day
ORDER BY day ASC

- Gordon Linoff

---

谢谢，那个完美地解决了问题！下一步是将它转换为Doctrine DQL...这会有点棘手！ - Pierre Roudaut

Answer 2

我可能错了，但在我看来，你正在寻找一种解决方案，其中有一个小组的相同用户？ 即使是这样，我也会改变逻辑方式，以避免生成查询并在更多用户时出现问题。 这可能不是您当前问题的完美解决方案，但可能有助于节省未来的一些时间。 因此，我会使用不同的模式。

作为查询，我将使用以下内容：

SELECT DATE(`date`) AS day,
  `author`,
  COUNT(`id`) AS messagecount
FROM `chat`
GROUP BY `day`, `author`
ORDER BY `day` ASC

使用此功能，您将得到如下结果：

+------------+--------+--------------+
|    day     | author | messagecount |
+------------+--------+--------------+
| 2016-01-01 | Mary   |            2 |
| 2016-01-01 | John   |            4 |
| 2016-01-02 | Mary   |            2 |
| 2016-01-02 | John   |            4 |
+------------+--------+--------------+

之后，您可以在PHP中对结果进行分组，以达到所需的效果，例如使用日期作为键生成像这样的数组：

array(
  '2016-01-01' => array(
    'day' => '2016-01-01',
    'Mary' => 2,
    'John' => 4
  ),
  '2016-01-02' => array(
    'day' => '2016-01-02',
    'Mary' => 2,
    'John' => 4
  ),
)

- Trendfischer