MySQL SELECT COUNT 具有多个字段

Question

我在 MySQL 表中有以下数据。

+---------------------+---------------------------+-----------------+
| AcctStartTime       | UserName                  |    ResponseCode |
+---------------------+---------------------------+-----------------+
| 2014-12-01 16:24:34 | 10000018@example.com |             408 |
| 2014-12-01 16:48:36 | 10000018@example.com |             200 |
| 2014-12-01 17:24:09 | 10000018@example.com |             408 |
| 2014-12-01 17:45:41 | 10000018@example.com |             200 |
| 2014-12-01 17:53:43 | 10000018@example.com |             408 |
| 2014-12-01 18:08:51 | 10000018@example.com |             200 |
| 2014-12-01 18:50:19 | 10000018@example.com |             200 |
| 2014-12-01 23:21:50 | 10000018@example.com |             502 |
| 2014-12-01 23:22:14 | 10000018@example.com |             487 |
| 2014-12-01 23:26:14 | 10000018@example.com |             484 |
| 2014-12-01 23:29:31 | 10000018@example.com |             408 |
| 2014-12-01 23:30:36 | 10000018@example.com |             487 |
| 2014-12-01 23:39:57 | 10000018@example.com |             487 |
| 2014-12-01 23:42:55 | 10000018@example.com |             200 |
| 2014-12-01 23:57:58 | 10000018@example.com |             200 |
| 2014-12-02 00:25:28 | 10000018@example.com |             200 |
| 2014-12-02 00:28:28 | 10000018@example.com |             200 |
| 2014-12-02 00:47:36 | 10000018@example.com |             200 |
| 2014-12-02 01:32:07 | 10000018@example.com |             200 |
| 2014-12-02 01:36:49 | 10000018@example.com |             200 |
| 2014-12-02 01:55:15 | 10000018@example.com |             200 |
| 2014-12-02 01:56:43 | 10000018@example.com |             200 |
| 2014-12-02 01:58:16 | 10000018@example.com |             487 |
| 2014-12-02 02:33:09 | 10000018@example.com |             484 |
| 2014-12-02 02:37:30 | 10000018@example.com |             408 |
| 2014-12-02 02:40:53 | 10000018@example.com |             487 |
| 2014-12-02 02:42:07 | 10000018@example.com |             484 |
| 2014-12-02 02:44:31 | 10000018@example.com |             200 |
| 2014-12-02 02:49:35 | 10000018@example.com |             408 |
| 2014-12-02 02:50:36 | 10000018@example.com |             408 |
| 2014-12-02 02:51:52 | 10000018@example.com |             200 |
| 2014-12-02 03:18:18 | 10000018@example.com |             487 |
| 2014-12-02 03:38:54 | 10000018@example.com |             200 |
| 2014-12-02 03:42:00 | 10000018@example.com |             200 |
| 2014-12-02 03:49:13 | 10000018@example.com |             200 |
| 2014-12-02 04:00:41 | 10000018@example.com |             487 |
| 2014-12-02 04:03:18 | 10000018@example.com |             487 |

我想知道每天我们得到多少 ResponsCode 计数，就像下面一样，我不知道如何在这里使用 GROUP 条件，我厌倦了很多选择，但没有得到想要的输出。

+---------------------+---------------+-----------+----------+
|      date           |       200     |    503    |    487   |
+---------------------+---------------+-----------+-----------
| 2014-12-01          |         5     |      3    |      7   |     
| 2014-12-02          |         8     |      1    |      2   |     
| 2014-12-03          |         4     |      0    |      9   |   

Answer 1

为此，您只需获取一个触摸广告素材，并在每个 ResponseCode 的 CASE 中添加 SUM:

SELECT DATE(AcctStartTime) AS `date`
    ,SUM(CASE ResponseCode
        WHEN 200 THEN 1
        ELSE 0
    END) AS `200`
    ,SUM(CASE ResponseCode
        WHEN 503 THEN 1
        ELSE 0
    END) AS `503`
    ,SUM(CASE ResponseCode
        WHEN 407 THEN 1
        ELSE 0
    END) AS `407`
FROM WhateverYourTableIs
GROUP BY `date`
;

这应该会为您提供所需的计数。