SQL使用WITH子句结合OR运算符查询出现重复记录问题咨询

问题根因 #

你将t3_table直接放在主查询的FROM子句中，和temp_data做全量关联，但f_data = 'N'的条件分支没有添加任何和t3_table的关联限制，所以每一条符合f_data='N'条件的temp_data记录，都会和t3_table里的所有记录做笛卡尔积拼接，如果t3_table刚好有180行数据，同一个账户自然会重复返回180次。

另外你原SQL还有一处语法错误：t3.f_DATE+t1.f_days中t1不在主查询作用域内，会执行报错，应该换成tmp_data.f_days。

排查解决步骤 #

• 优先从逻辑层面避免笛卡尔积产生：两个条件分支的查询逻辑完全独立，直接拆分查询后用UNION ALL合并结果即可，可读性和性能都更优
• 如果不希望拆分查询，可以调整JOIN写法，把t3表的关联条件放到JOIN的ON子句中，再通过DISTINCT对结果去重即可

方案1：UNION ALL拆分逻辑（推荐） #

WITH temp_data AS(
    SELECT t1.acc_name,t1.f_data,t1.EXPIRY_DATE,t1.f_days
    FROM t1_table t1, t2_table t2 
    WHERE t1.acc_name = t2.acc_name
      AND t2.FLAG = 2
)            
-- 查f_data='N'的账户
SELECT tmp_data.acc_name 
FROM temp_data tmp_data  
WHERE tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE
UNION ALL
-- 查f_data='Y'的账户
SELECT tmp_data.acc_name 
FROM temp_data tmp_data
INNER JOIN t3_table t3 ON tmp_data.acc_name = t3.acc_name
WHERE tmp_data.f_data = 'Y'
  AND (
        (t3.f_interval_period <> -1 AND t3.f_DATE <= SYSDATE)
        OR (t3.f_interval_period = -1 AND t3.f_DATE + tmp_data.f_days <= SYSDATE)
      )

方案2：调整JOIN逻辑 #

WITH temp_data AS(
    SELECT t1.acc_name,t1.f_data,t1.EXPIRY_DATE,t1.f_days
    FROM t1_table t1, t2_table t2 
    WHERE t1.acc_name = t2.acc_name
      AND t2.FLAG = 2
)            
SELECT DISTINCT tmp_data.acc_name 
FROM temp_data tmp_data
LEFT JOIN t3_table t3 ON tmp_data.acc_name = t3.acc_name
WHERE (tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)
   OR (
        tmp_data.f_data = 'Y' 
        AND t3.acc_name IS NOT NULL
        AND (
              (t3.f_interval_period <> -1 AND t3.f_DATE <= SYSDATE)
              OR (t3.f_interval_period = -1 AND t3.f_DATE + tmp_data.f_days <= SYSDATE)
            )
      )

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