SQL中如何实现带全值条件判断的分组聚合计算

源表结构参考
现有两列表t1，C2字段存储多组数值，需按C1分组生成结果表t2：
期望输出结果参考
t2.c2字段规则：分组内所有t1.C2值均<=5时返回1，否则返回0。

纯分组统计场景下，基于极值判断的聚合写法是性能最高、通用性最强的实现，完全适配所有主流关系型数据库（MySQL、PostgreSQL、SQL Server、Oracle等），且能最大化利用数据库的聚合优化、索引优化能力：

SELECT
  C1,
  CASE WHEN MAX(C2) <= 5 THEN 1 ELSE 0 END AS C2
FROM t1
GROUP BY C1;

方案优势 #

• 逻辑等价：只要组内存在任意一个C2>5，组内C2的最大值必然大于5，判断最大值是否<=5和「所有值均<=5」的逻辑完全等价，不存在逻辑漏洞
• 性能最优：MAX()聚合是数据库原生支持的基础聚合操作，执行开销远低于逐行判断计数、子查询匹配、窗口函数等写法；如果C1、C2字段建有联合索引，数据库可以直接通过索引遍历拿到分组极值，不需要扫描全表数据

其他场景适配写法 #

如果需求不是输出纯分组聚合结果，而是要在原表所有明细行上附带对应分组的C2标记，可以用窗口函数实现，注意纯分组统计场景下该写法性能弱于上面的聚合方案：

SELECT
  C1,
  C2 AS original_c2, -- 原表明细C2值
  CASE WHEN MAX(C2) OVER(PARTITION BY C1) <=5 THEN 1 ELSE 0 END AS group_c2
FROM t1;

不推荐写法 #

以下写法虽然能实现逻辑，但性能、通用性存在明显缺陷：

• 逐行计数判断：比如CASE WHEN SUM(CASE WHEN C2>5 THEN 1 ELSE 0 END) = 0 THEN 1 ELSE 0 END，需要逐行做条件判断计数，数据量越大性能差距越明显
• 数据库专属逻辑聚合：比如PostgreSQL的BOOL_AND(C2<=5)::INT、MySQL的BIT_AND(C2<=5)，跨数据库兼容性差，迁移成本高
• 关联子查询判断：比如通过NOT EXISTS匹配组内是否存在C2>5的记录，执行时会产生嵌套查询开销，性能远低于原生聚合

内容的提问来源于stack exchange，提问作者Harshith S H