mysql - 我如何计算状态

Question

我有这个表结构

CREATE TABLE `table_detail` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `status` CHAR(2) NOT NULL,
    `dateTime_updated` DATETIME NOT NULL,
    PRIMARY KEY (`id`),

)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

;

我想统计指定的用户状态为 7,6 和 4查询 2015-09-07 06:00:00 - 2015-09-11 20:00:00

的日期时间

7=事件、6=非事件、4=已删除

预期输出

+---------+--------+----------+---------+
| user_id | active | inactive | deleted |
+---------+--------+----------+---------+
|      55 |      2 |        3 |       0 |
|      26 |      4 |        6 |       3 |
|      15 |      3 |        7 |       7 |
|      24 |      6 |        3 |       3 |
+---------+--------+----------+---------+

提前谢谢您。

Answer 1

您可以为此使用条件聚合。您需要做的就是在 select 子句中添加 SUM(condition) 列，并按 user_id 值进行分组，如下所示:

SELECT user_id, 
   SUM(status = 7) AS active, 
   SUM(status = 6) AS inactive, 
   SUM(status = 4) AS deleted
FROM table_detail
WHERE dateTime_updated BETWEEN '2015-09-07 06:00:00' AND '2015-09-11 20:00:00'
GROUP BY user_id;