T-SQL多表LEFT JOIN后count统计值远大于实际值的解决方法

问题现象 #

编写的Transact SQL查询执行后，Count字段返回值远大于真实业务值，原始查询语句如下：

SELECT max(l.Date)
      ,count(stp.Key) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY count(stp.Key) DESC

将count(stp.Key)替换为相关子查询(select count(Key) from Profile where wm.Id = Id)后，可以得到符合预期的统计结果，修改后查询如下：

SELECT max(l.Date)
      ,(select count(Key) from Profile where wm.Id = Id) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY (select count(Key) from Profile where wm.Id = Id) DESC

实际运行对比：普通COUNT写法返回排名第一的结果统计值为147000，子查询写法对应结果仅为65。初步判断原写法统计了多表连接后生成的笛卡尔积记录，需要实现按wm.Id分组，仅统计匹配的stp.Key记录数。

补充表结构样例 #

表：Web

Id
1
2
3

表：Profile

Id Key
1  10
1  20
2  30
2  40
3  50
4  60

表：Log

Id Date
1  2022-06-12
1  2022-06-02
2  2022-06-23
2  2022-06-01
3  2022-06-14
3  2022-06-03

最终需求：按wm.Id分组，统计每条Web记录关联的Profile表匹配行数，同时取关联Log表的最大日期，避免多表JOIN产生的笛卡尔积导致统计失真。

问题根因 #

判断完全准确：连续对两个存在一对多关系的表做LEFT JOIN时，必然会产生笛卡尔积——同一条Web记录，每匹配一条Profile记录，就会和所有匹配的Log记录两两组合，最终JOIN后的行数是「Profile匹配数 * Log匹配数」，直接COUNT会把这些组合出来的重复行全部算入统计，数值自然异常偏大，两边匹配的记录越多，偏差倍数越高。以给出的样例数据为例，Web.Id=1匹配2条Profile、2条Log，JOIN后实际生成4行，直接COUNT会返回4而不是真实的2，和147000 vs 65的偏差逻辑完全一致。

正确实现方式 #

写法1：预聚合后关联（性能最优，生产环境推荐） #

先分别对Profile、Log表按关联键做聚合计算，再和Web表JOIN，从根源避免笛卡尔积产生，性能远高于逐行执行的相关子查询：

SELECT 
  l.MaxDate,
  ISNULL(stp.ProfileCount, 0) AS Count,
  wm.Id
FROM Web wm
LEFT JOIN (
  -- 先按关联键统计每个Web对应的Profile行数
  SELECT WebId, COUNT(Key) AS ProfileCount
  FROM Profile
  GROUP BY WebId
) stp ON wm.WebId = stp.WebId
LEFT JOIN (
  -- 先按关联键统计每个Web对应的最大Log日期
  SELECT WebId, MAX(Date) AS MaxDate
  FROM Log
  GROUP BY WebId
) l ON wm.WebId = l.WebId
WHERE wm.Code = 'RR'
ORDER BY stp.ProfileCount DESC

写法2：去重计数（适合小数据量快速验证场景） #

如果stp.Key是全局唯一的非空值，可以直接用COUNT(DISTINCT)去重计数，不需要修改原有JOIN结构，写法更简洁：

SELECT max(l.Date)
      ,count(DISTINCT stp.Key) as 'Count'
      ,wm.Id
  FROM Web wm
  LEFT JOIN Profile stp ON wm.WebId = stp.WebId
  LEFT JOIN Log l ON wm.WebId = l.WebId
  WHERE Code = 'RR'
  GROUP By wm.Id
  ORDER BY count(DISTINCT stp.Key) DESC

注意：如果stp.Key存在重复值，该写法会出现统计错误，优先选择预聚合写法。

相关子查询为什么能返回正确结果 #

相关子查询是针对结果集里的每一个wm.Id，单独到Profile表执行一次计数，完全不受Log表JOIN产生的重复行影响，逻辑和预聚合写法一致，但相关子查询会逐行执行扫描，数据量较大时性能远差于预聚合方案。

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