Oracle多表查询出现重复记录问题求助（附SQL代码）

问题根源 #

你的查询仅通过applicant_id关联bres_reg和support_service表，当一个申请人存在多条reg_date记录时，每条support_service记录会与所有reg_date记录做笛卡尔积匹配，导致金额在每个reg_date下重复显示。

解决方案 #

需要在LEFT JOIN support_service的关联条件中，补充**support_service记录与bres_reg中对应reg_date的归属关联逻辑**，以下是几种可行方案：

1. 基于直接关联字段匹配（优先推荐） #

如果support_service表中存在对应bres_reg的主键字段（比如reg_id），直接添加该关联条件即可精准匹配：

SELECT b.applicant_id,
       b.program_code,
       b.last_name,
       b.first_name,
       a.username,
       b.region_code,
       b.reg_date,
       b.status_cd,
       s.first_name AS "Staff",
       s.last_name  AS "Staff2",
       b.exit_date,
       ss.program_code,
       ss.support_service_amt,
       ss.expenditure_begin_date,
       ss.expenditure_end_date   
  FROM bres_reg b
  LEFT JOIN support_service ss
    ON ss.applicant_id = b.applicant_id
   AND ss.reg_id = b.reg_id -- 添加与bres_reg主键的直接关联
  LEFT JOIN staff s
    ON s.ID = b.case_manager_staff_id                    
  LEFT JOIN applicant a
    ON b.applicant_id = a.ID
 WHERE ss.program_code = 'BRES'

2. 基于日期范围匹配 #

如果没有直接关联字段，可通过reg_date与support_service的支出日期范围匹配（比如该笔支出属于当前注册周期内）：

SELECT b.applicant_id,
       b.program_code,
       b.last_name,
       b.first_name,
       a.username,
       b.region_code,
       b.reg_date,
       b.status_cd,
       s.first_name AS "Staff",
       s.last_name  AS "Staff2",
       b.exit_date,
       ss.program_code,
       ss.support_service_amt,
       ss.expenditure_begin_date,
       ss.expenditure_end_date   
  FROM bres_reg b
  LEFT JOIN support_service ss
    ON ss.applicant_id = b.applicant_id
   AND ss.expenditure_begin_date >= b.reg_date
   AND (b.exit_date IS NULL OR ss.expenditure_end_date <= b.exit_date)
  LEFT JOIN staff s
    ON s.ID = b.case_manager_staff_id                    
  LEFT JOIN applicant a
    ON b.applicant_id = a.ID
 WHERE ss.program_code = 'BRES'

3. 分组聚合去重（无明确关联逻辑时使用） #

如果找不到精准关联规则，可通过分组聚合合并同一applicant_id+reg_date下的重复金额：

SELECT b.applicant_id,
       b.program_code,
       b.last_name,
       b.first_name,
       a.username,
       b.region_code,
       b.reg_date,
       b.status_cd,
       s.first_name AS "Staff",
       s.last_name  AS "Staff2",
       b.exit_date,
       ss.program_code,
       SUM(ss.support_service_amt) AS support_service_amt,
       MIN(ss.expenditure_begin_date) AS expenditure_begin_date,
       MAX(ss.expenditure_end_date) AS expenditure_end_date   
  FROM bres_reg b
  LEFT JOIN support_service ss
    ON ss.applicant_id = b.applicant_id
  LEFT JOIN staff s
    ON s.ID = b.case_manager_staff_id                    
  LEFT JOIN applicant a
    ON b.applicant_id = a.ID
 WHERE ss.program_code = 'BRES'
 GROUP BY b.applicant_id,
          b.program_code,
          b.last_name,
          b.first_name,
          a.username,
          b.region_code,
          b.reg_date,
          b.status_cd,
          s.first_name,
          s.last_name,
          b.exit_date,
          ss.program_code

注意事项 #

• 优先选择直接关联字段或日期范围匹配，这两种方式能保证数据的准确性；分组聚合是无法找到精准关联时的退而求其次方案。
• 测试时可单独查询bres_reg与support_service的关联数据，验证关联条件是否正确过滤了重复记录。

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