Microsoft SQL Server中用SELECT子查询按ID分组计数的问题咨询

问题1：如何通过子查询实现需要的分组统计效果 #

你当前返回同一个ID多条计数为1的记录，核心原因是外层查询没有对ID聚合，tbl1中同一个ID存在多行符合Var2=80的记录，每行都会单独计算该行Var对应的tbl2关联行数。
正确的子查询写法如下：

SELECT
    tbl1.ID,
    (
        SELECT COUNT(*)
        FROM tbl2
        WHERE tbl2.Var IN (
            -- 取当前ID下所有符合条件的Var值去匹配tbl2
            SELECT Var 
            FROM firstTable 
            WHERE ID = tbl1.ID AND Var2 = 80
        )
    ) AS TheCount
FROM firstTable AS tbl1
WHERE tbl1.Var2 = 80
GROUP BY tbl1.ID
ORDER BY tbl1.ID

如果确认同一个ID在tbl1中对应的Var不会重复，也可以简化为外层先聚合ID，子查询直接关联外层聚合后的ID对应的Var即可。

问题2：子查询中没有出现ID字段，为何当前返回结果中ID和TheCount能正确对应？ #

你写的是相关子查询，执行逻辑是先遍历外层查询的每一行记录，将当前行的tbl1.Var作为参数传入子查询的WHERE条件做匹配，所以每一行的TheCount计算结果天然和当前行的ID字段一一对应，不需要在子查询中显式声明ID字段。

问题3：这类统计场景下选择子查询和JOIN分别有什么优劣，如何抉择？ #

子查询的优劣 #

• 优势：
  • 扩展成本极低，新增其他表的统计列时只需要新增对应的子查询即可，不需要修改外层聚合逻辑，也不会出现多表JOIN导致的笛卡尔积计数错误问题
  • 逻辑直观，不需要处理JOIN后的去重、关联类型匹配等问题
• 劣势：
  • 每增加一个统计子查询，就需要多扫描一次关联表，数据量较大时性能远低于JOIN方案
  • 复杂关联条件下子查询会出现大量重复逻辑，维护成本升高

JOIN的优劣 #

• 优势：
  • 所有关联表只需要扫描一次，聚合计算效率更高，适合大数据量的统计场景
  • 复杂多字段关联、多维度聚合场景下逻辑更统一
• 劣势：
  • 一对多、多对多关联时很容易产生笛卡尔积，导致计数、求和等聚合结果错误，需要额外做去重处理
  • 新增其他表的统计列时需要修改JOIN逻辑，还要校验对现有聚合结果的影响，扩展成本高

抉择建议 #

• 如果需要统计的关联列多、数据量不大、需求迭代频繁，优先选择子查询方案，开发维护成本更低、不易出错
• 如果数据量很大、统计逻辑固定，优先选择JOIN方案，性能表现更好

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