如何简化重复使用SELECT COUNT(*)的T-SQL查询并保留6行输出结果
T-SQL重复COUNT查询简化方案
你可以通过单次表扫描+条件聚合+行转列的方式简化查询,完全保留原输出6行的效果,同时大幅降低表扫描次数,提升查询性能:
优化后代码
SELECT cnt FROM ( SELECT -- 每个CASE对应原UNION ALL的一个WHERE筛选逻辑 SUM(CASE WHEN columnA = 'I' AND columnB = 'I' AND columnC = '' THEN 1 ELSE 0 END) AS cnt1, SUM(CASE WHEN columnA = 'I' AND columnB = 'I' AND columnC <> '' THEN 1 ELSE 0 END) AS cnt2, SUM(CASE WHEN columnA IN ('A','R') AND columnB = 'I' AND columnD IN (SELECT columnE FROM Table2) THEN 1 ELSE 0 END) AS cnt3, SUM(CASE WHEN columnA = 'B' AND columnB = 'I' THEN 1 ELSE 0 END) AS cnt4, SUM(CASE WHEN columnA = 'R' AND columnB = 'S' THEN 1 ELSE 0 END) AS cnt5, SUM(CASE WHEN columnA = 'A' AND columnB = 'S' THEN 1 ELSE 0 END) AS cnt6 FROM [Table] ) AS calc_result -- 将6个计数值转成6行输出,顺序和原查询完全一致 UNPIVOT ( cnt FOR count_type IN (cnt1, cnt2, cnt3, cnt4, cnt5, cnt6) ) AS unpivot_result
方案说明
- 输出结果和原
UNION ALL写法完全一致,行顺序、计数数值没有偏差 - 仅对目标表执行1次扫描,避免了原写法6次重复扫表的IO开销,数据量越大性能提升越明显
- 如果需要识别每行计数对应的业务规则,可以保留输出
count_type字段,自行给每个计数规则添加对应备注即可
内容的提问来源于stack exchange,提问作者KelveenRaj
相关产品推荐
相关产品推荐

