如何用单条SQL查询统计指定值在列中的出现次数（性能优化）

核心方案 #

通过构造待统计值的临时数据集，与原表左连接后分组统计，既满足行式输出要求，又能保证百万级数据下的性能。

各数据库实现代码 #

MySQL/SQL Server

SELECT 
    t.code,
    COUNT(h.code_list) AS count
FROM 
    (
        SELECT '627czl' AS code UNION ALL
        SELECT '1lqnd8' AS code UNION ALL
        SELECT 'esdop9' AS code UNION ALL
        SELECT 'aol4m6' AS code
        -- 如需统计更多值，继续添加UNION ALL行即可
    ) t
LEFT JOIN history h ON t.code = h.code_list
GROUP BY t.code;

PostgreSQL

SELECT 
    t.code,
    COUNT(h.code_list) AS count
FROM 
    (VALUES 
        ('627czl'), ('1lqnd8'), ('esdop9'), ('aol4m6')
        -- 如需统计更多值，继续添加括号内的字符串即可
    ) AS t(code)
LEFT JOIN history h ON t.code = h.code_list
GROUP BY t.code;

性能优化关键 #

• 给history表的code_list列创建普通索引：

  CREATE INDEX idx_history_code_list ON history(code_list);
  

  索引能让数据库快速定位匹配的行，避免全表扫描，百万级数据下性能提升显著。
• 采用UNION ALL（或PostgreSQL的VALUES）构造临时数据集，比子查询或临时表更高效。
• 使用COUNT(h.code_list)而非COUNT(*)，确保未匹配到的code统计值为0。

为什么不推荐列式统计 #

如果用CASE WHEN逐个生成列的方式，每个统计值都需要遍历一次表，10个值就要扫10次表，性能远低于左连接的单次扫描方案，且输出格式不符合需求。

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