case when then 用法
表结构
有如下表结构：
| ID | event_id | store_id | status |
|---|---|---|---|
| 1 | 1 | A001 | 0 |
| 2 | 1 | A001 | 1 |
| 3 | 1 | A001 | 1 |
| 4 | 1 | A002 | 0 |
| 5 | 1 | A002 | 1 |
| 6 | 1 | A002 | 0 |
| 7 | 2 | A001 | 0 |
| 8 | 2 | A001 | 1 |
| 9 | 2 | A001 | 1 |
| 10 | 2 | A002 | 1 |
| 11 | 2 | A002 | 1 |
| 12 | 2 | A002 | 0 |
准备表结构和数据
创建表
CREATE TABLE `table1` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`event_id` INT(10) UNSIGNED NOT NULL,
`store_id` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
`status` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) USING BTREE
)
填充数据
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A001', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A002', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('1', 'A003', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A001', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A002', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('2', 'A003', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A001', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A002', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('3', 'A003', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A001', '0');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A002', '1');
INSERT INTO `test`.`table1` (`event_id`, `store_id`, `status`) VALUES ('4', 'A003', '0');
需求
需求：根据 event_id 查出如下对应信息，参与状态为 status 字段，1 已完成，0 未完成
| 门店ID | 参与人数(0,1) | 完成人(1) | 未完成人(0) |
|---|---|---|---|
| A001 | 3 | 2 | 1 |
| A002 | 3 | 1 | 2 |
思路一：采用 case when then
select
store_id,count(store_id),
sum(CASE `status` WHEN '1' then '1' ELSE '0' end ) as num1,
sum(CASE `status` WHEN '0' then '1' ELSE '0' end ) as num0
FROM table1
WHERE
event_id = 1
GROUP BY store_id
思路二：使用 if
参考链接：https://blog.csdn.net/lilong329329/article/details/81664451
项目中用到的真实查询 SQL
select
a.store_id,count(a.store_id),
sum(CASE `status` WHEN '1' then '1' ELSE '0' end ) as num1,
sum(CASE `status` WHEN '0' then '1' ELSE '0' end ) as num0,
b.type
FROM t_rush_buy_event_successfuls as a LEFT JOIN t_store as b
on a.event_id = b.event_id and a.store_id = b.serial_number
WHERE
a.event_id = 156
GROUP BY a.store_id