左外连接表计数不正确问题排查与技术咨询

嘿，我完全懂你遇到的这个头疼问题——左外连接后统计USRID数量，结果PS_HS_AUD和PS_HS_PRE各差1条，总共偏了2条，而且你已经定位到是某个USRID在PS_HS_ANN里有2条不同EXAM_DT的记录搞的鬼，还加了MAX(EXAM_DT)的条件但没解决？那大概率是你加条件的时机不对，导致重复行还是没被过滤掉。

问题到底出在哪？ #

你想啊，如果你直接把MAX(EXAM_DT)放在WHERE或者JOIN条件里，但没先把PS_HS_ANN的记录按USRID聚合好，那数据库会先把主表和PS_HS_ANN的两条记录都连起来，再去筛选最新日期的那条——这时候主表的那个USRID已经被拆成两行数据了！后续和PS_HS_AUD、PS_HS_PRE连接时，自然会把这两行都算进去，导致每个表的计数都多了1条，加起来就偏2条。

举个简化的例子，假设你原来的查询是这样的：

SELECT 
  COUNT(DISTINCT main.USRID) AS total,
  COUNT(aud.USRID) AS aud_count,
  COUNT(pre.USRID) AS pre_count
FROM main_table main
LEFT JOIN PS_HS_AUD aud ON main.USRID = aud.USRID
LEFT JOIN PS_HS_PRE pre ON main.USRID = pre.USRID
LEFT JOIN PS_HS_ANN ann ON main.USRID = ann.USRID
WHERE ann.EXAM_DT = (SELECT MAX(EXAM_DT) FROM PS_HS_ANN WHERE USRID = main.USRID)

这种写法里，PS_HS_ANN的两条记录先和主表连接生成两行，再过滤掉旧日期的那条，但主表的USRID已经被重复计算过一次了，所以计数就偏了。

怎么解决？ #

核心思路是先把PS_HS_ANN的记录处理干净，再和其他表连接——也就是先给每个USRID只保留最新EXAM_DT的那条记录，再去做左连接，这样就不会产生重复行了。

给你写个具体的调整方案：

1. 先用CTE预生成PS_HS_ANN的去重结果，只留每个用户的最新记录：

WITH latest_ann_records AS (
  SELECT USRID, MAX(EXAM_DT) AS latest_exam_dt
  FROM PS_HS_ANN
  GROUP BY USRID
)

2. 然后用这个干净的CTE和其他表做连接，如果需要PS_HS_ANN里的其他字段，还可以再连一次原始表：

SELECT 
  COUNT(DISTINCT main.USRID) AS total_users,
  COUNT(aud.USRID) AS aud_count,
  COUNT(pre.USRID) AS pre_count
FROM main_table main
LEFT JOIN PS_HS_AUD aud ON main.USRID = aud.USRID
LEFT JOIN PS_HS_PRE pre ON main.USRID = pre.USRID
-- 先连预聚合好的ANN表，确保每个USRID只匹配一次
LEFT JOIN latest_ann_records ann ON main.USRID = ann.USRID
-- 如果要取ANN的其他字段，再左连接一次原始表，用USRID+最新日期匹配
-- LEFT JOIN PS_HS_ANN ann_detail ON ann.USRID = ann_detail.USRID AND ann.latest_exam_dt = ann_detail.EXAM_DT

额外验证小技巧 #

你可以先单独查那个有问题的USRID，看看各个表的记录数：

SELECT 
  (SELECT COUNT(*) FROM PS_HS_AUD WHERE USRID = '你的问题USRID') AS aud_num,
  (SELECT COUNT(*) FROM PS_HS_PRE WHERE USRID = '你的问题USRID') AS pre_num,
  (SELECT COUNT(*) FROM PS_HS_ANN WHERE USRID = '你的问题USRID') AS ann_num

然后再跑调整后的查询，看看这个USRID是不是只出现一行——只要重复行没了，计数就会准确了。

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