如何在T-SQL中用非子查询实现多条件高效计数（大数据表）

嘿，你的思路完全正确！用条件聚合代替子查询正是解决这个超1亿行大表统计性能问题的最优方案，而且写法和你预想的伪代码非常接近。

为什么子查询写法效率低？ #

你原来的子查询写法会对目标表进行两次完整扫描（主查询一次，子查询一次），对于1亿行的表来说，这会带来双倍的IO开销，性能损耗非常明显。而且如果子查询返回多行数据，还会直接抛出错误，稳定性也有问题。

最优实现方案（条件聚合） #

直接在COUNT或SUM函数里嵌入CASE表达式，实现单扫描多条件统计，完美适配你的聚集列存储索引（列存储索引天生擅长批量扫描和聚合操作）：

SELECT 
    v.name,
    -- COUNT会忽略NULL，满足条件返回1，否则返回NULL，刚好统计符合条件的行数
    COUNT(CASE WHEN v.value = 1 THEN 1 END) AS v1,
    COUNT(CASE WHEN v.value = 2 THEN 1 END) AS v2
FROM dbo.table AS v
WHERE 
    v.organisationID = 2 
    AND v.datecreated > '2018-01-01' 
    AND v.datecreated < '2018-05-01'
GROUP BY v.name

如果你更习惯用SUM，也可以写成这样，效果完全一致：

SELECT 
    v.name,
    SUM(CASE WHEN v.value = 1 THEN 1 ELSE 0 END) AS v1,
    SUM(CASE WHEN v.value = 2 THEN 1 ELSE 0 END) AS v2
FROM dbo.table AS v
WHERE 
    v.organisationID = 2 
    AND v.datecreated > '2018-01-01' 
    AND v.datecreated < '2018-05-01'
GROUP BY v.name

性能优势说明 #

• 仅需扫描一次表，相比子查询写法减少50%的IO开销，对于1亿行的大表来说，性能提升非常显著
• 条件聚合逻辑可以被SQL Server查询优化器完美识别，充分利用聚集列存储索引的批量处理能力，进一步降低查询延迟
• 写法简洁直观，可读性远高于嵌套子查询，后续维护也更方便

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