Mysql获取多列计数

Question

如果我有一张表格显示门上的条目，如下所示:

+---+---------------------+------+------+
|id | entry_time          | user | door |
+---+---------------------+------+------+
|1  | 2018-08-28 12:31:58 | 12   | A    |
|2  | 2018-08-28 14:39:08 | 12   | A    |
|3  | 2018-08-28 15:22:36 | 12   | B    |
|4  | 2018-09-10 10:06:59 | 14   | C    |
|5  | 2018-09-11 09:21:57 | 14   | A    |
|6  | 2018-09-11 09:44:25 | 14   | A    |
|7  | 2018-09-11 10:24:55 | 14   | B    |
|8  | 2018-09-11 13:50:14 | 14   | C    |
|9  | 2018-09-12 11:57:11 | 14   | A    |
|10 | 2018-09-14 08:26:30 | 14   | B    |
|11 | 2018-09-15 10:45:07 | 17   | B    |
|12 | 2018-09-15 11:01:12 | 10   | C    |
|13 | 2018-09-15 11:06:02 | 8    | A    |
|14 | 2018-09-15 11:41:13 | 21   | B    |
+---+---------------------+------+------+

我如何构造我的查询来实现这一点？

+------------+---------+-------+--------+--------+--------+
| entry_hour | entries | users | door A | door B | door C |
+------------+---------+-------+--------+--------+--------+
| 0800-0900  | 1       | 1     | 0      | 1      | 0      |
| 0900-1000  | 2       | 1     | 2      | 0      | 0      |
| 1000-1100  | 3       | 2     | 0      | 2      | 1      |
| 1100-1200  | 4       | 3     | 2      | 1      | 1      |
| 1200-1300  | 1       | 1     | 1      | 0      | 0      |
| 1300-1400  | 1       | 1     | 0      | 0      | 1      |
| 1400-1500  | 1       | 1     | 1      | 0      | 0      |
| 1500-1600  | 1       | 1     | 0      | 1      | 0      |
+------------+---------+-------+--------+--------+--------+

我知道如何获取第一列和第二列，但无法理解其他列。这是我到目前为止所拥有的:

SELECT
    CONCAT(TIME_FORMAT(entry_time, '%H00'),'-',TIME_FORMAT(DATE_ADD(entry_time,INTERVAL 1 HOUR), '%H00')) AS entry_hour,
    COUNT(id) AS 'entries'
FROM
    test
GROUP BY entry_hour
ORDER BY entry_hour;

如果有人想尝试一下，这里是 CREATE 语句:

CREATE TABLE `test` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `entry_time` datetime DEFAULT NULL,
  `user` int(2) DEFAULT NULL,
  `door` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `tmp_test` VALUES ('1', '2018-08-28 12:31:58', '12', 'A'), ('2', '2018-08-28 14:39:08', '12', 'A'), ('3', '2018-08-28 15:22:36', '12', 'B'), ('4', '2018-09-10 10:06:59', '14', 'C'), ('5', '2018-09-11 09:21:57', '14', 'A'), ('6', '2018-09-11 09:44:25', '14', 'A'), ('7', '2018-09-11 10:24:55', '14', 'B'), ('8', '2018-09-11 13:50:14', '8', 'C'), ('9', '2018-09-12 11:57:11', '14', 'A'), ('10', '2018-09-14 08:26:30', '14', 'B'), ('11', '2018-09-15 10:45:07', '17', 'B'), ('12', '2018-09-15 11:01:12', '10', 'B'), ('13', '2018-09-15 11:06:02', '8', 'A'), ('14', '2018-09-15 11:41:13', '21', 'B');
COMMIT;

Answer 1

在 CASE WHEN 中使用条件聚合

SELECT
    CONCAT(TIME_FORMAT(entry_time, '%H00'),'-',TIME_FORMAT(DATE_ADD(entry_time,INTERVAL 1 HOUR), '%H00')) AS entry_hour,
    COUNT(id) AS 'entries',
count(case when door='A' then id end) as doorA,
count(case when door='B' then id end) as doorB,
count(case when door='C' then id end) as doorC
FROM
    test
GROUP BY entry_hour
ORDER BY entry_hour