如何在同一张表中结合WHERE子句多次使用SQL COUNT()函数

原表结构 #

当前的两个查询语句 #

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '911%'

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '111%'

合并后的查询语句 #

可以通过条件聚合实现一次查询得到目标结果，两种写法均可行：

写法一（COUNT配合CASE）

SELECT
    COUNT(CASE WHEN terminal_id LIKE '911%' THEN name END) AS `911like`,
    COUNT(CASE WHEN terminal_id LIKE '111%' THEN name END) AS `111like`
FROM `table`
WHERE created_at BETWEEN '2022-12-01' AND '2022-12-10';

写法二（SUM配合CASE）

SELECT
    SUM(CASE WHEN terminal_id LIKE '911%' THEN 1 ELSE 0 END) AS `911like`,
    SUM(CASE WHEN terminal_id LIKE '111%' THEN 1 ELSE 0 END) AS `111like`
FROM `table`
WHERE created_at BETWEEN '2022-12-01' AND '2022-12-10';

说明 #

两种写法核心都是用CASE WHEN判断字段匹配条件，再通过聚合函数统计符合要求的记录数。相比两次单独查询，这种方式仅需扫描一次表，效率更高，且直接输出你需要的列格式。

内容的提问来源于stack exchange，提问作者developeruser