Oracle计算逻辑问题：RESULT2_FINAL表未正确插入RYAN、TONY记录

源表结构及数据 #

以下是4个源表的创建及数据插入语句（已修正原语句中VALUESS的笔误）：

CREATE TABLE TABLE1_NAMES (NAMES VARCHAR2(12)); 

INSERT INTO TABLE1_NAMES VALUES ('JOHN');
INSERT INTO TABLE1_NAMES VALUES ('RYAN');
INSERT INTO TABLE1_NAMES VALUES ('OLAN');
INSERT INTO TABLE1_NAMES VALUES ('TONY');

CREATE TABLE TABLE2_LINES (NAMES VARCHAR2(12), LINE_NO NUMBER, UNITS NUMBER(18,4), CODE VARCHAR2(4));

INSERT INTO TABLE2_LINES VALUES ('JOHN',1,1,'101');
INSERT INTO TABLE2_LINES VALUES ('JOHN',2,1,'202');
INSERT INTO TABLE2_LINES VALUES ('JOHN',3,1,'180');
INSERT INTO TABLE2_LINES VALUES ('JOHN',4,2,'300');

INSERT INTO TABLE2_LINES VALUES ('RYAN',1,2,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',2,1,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',3,1,'500');

INSERT INTO TABLE2_LINES VALUES ('OLAN',1,1,'301');

INSERT INTO TABLE2_LINES VALUES ('TONY',1,1,'201');

CREATE TABLE TABLE3_DATES (NAMES VARCHAR2(12), FR_DT TIMESTAMP(3), TO_DT TIMESTAMP(3), START_DT TIMESTAMP(3),END_DT TIMESTAMP(3));

INSERT INTO TABLE3_DATES VALUES ('JOHN','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');
INSERT INTO TABLE3_DATES VALUES ('RYAN','01-DEC-22 12.00.00.000000000 AM','04-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');
INSERT INTO TABLE3_DATES VALUES ('OLAN','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');

INSERT INTO TABLE3_DATES VALUES ('TONY','01-DEC-22 12.00.00.000000000 AM','05-DEC-22 12.00.00.000000000 AM','03-DEC-22 12.00.00.000000000 AM','09-DEC-22 12.00.00.000000000 AM');

CREATE TABLE TABLE4_CODES (CD_NM VARCHAR2(12), B_CODE VARCHAR2(4), E_CODE VARCHAR2(4));
 
INSERT INTO TABLE4_CODES VALUES ('CODELIST','100','101');
INSERT INTO TABLE4_CODES VALUES ('CODELIST','180','180');
INSERT INTO TABLE4_CODES VALUES ('CODELIST','200','219');
COMMIT;

结果表结构 #

用于计算和存储最终结果的两个表：

CREATE TABLE RESULT1_CALC (NAMES VARCHAR2(12), FR_DT TIMESTAMP(3), TO_DT TIMESTAMP(3), START_DT TIMESTAMP(3), END_DT TIMESTAMP(3), TOT_UNITS NUMBER);

CREATE TABLE RESULT2_FINAL (NAMES VARCHAR2(12), EX_CD VARCHAR2(3));

业务逻辑说明 #

基于TABLE2_LINES中属于TABLE4_CODES的CODE值，计算每人的SUM(UNITS)，并按以下规则校验：

1. 若人员仅存在CODE '180'，将SUM(UNITS)与TO_DT - FR_DT的天数对比，不等则将姓名+'ABC'插入RESULT2_FINAL；
2. 若人员同时存在CODE '180'与其他合格CODE，将SUM(UNITS)与END_DT - START_DT的天数对比，不等则插入RESULT2_FINAL；
3. 若人员不存在CODE '180'但存在其他合格CODE，同样将SUM(UNITS)与END_DT - START_DT的天数对比，不等则插入RESULT2_FINAL。

案例说明 #

• JOHN：拥有合格CODE 101、202、180，SUM(UNITS)=3，与TO_DT - FR_DT（4天）不等，需插入RESULT2_FINAL；
• RYAN：仅拥有合格CODE 180，SUM(UNITS)=3，与END_DT - START_DT（6天）不等，需插入RESULT2_FINAL；
• OLAN：无合格CODE，无需处理；
• TONY：拥有合格CODE 201但无180，SUM(UNITS)=1，与END_DT - START_DT（6天）不等，需插入RESULT2_FINAL。

现有SQL问题 #

现有SQL无法将RYAN和TONY记录插入RESULT2_FINAL表，代码如下：

INSERT INTO RESULT1_CALC 
(
  SELECT T1.NAMES, T3.FR_DT, T3.TO_DT, T3.START_DT, T3.END_DT, RES.TOT_UNITS
  FROM TABLE1_NAMES T1
       JOIN (
         SELECT T2.NAMES, SUM(T2.UNITS) AS TOT_UNITS
         FROM TABLE2_LINES T2
         JOIN TABLE4_CODES T4
         ON T4.CD_NM = 'CODELIST'
         AND T2.CODE BETWEEN T4.B_CODE AND T4.E_CODE
         GROUP BY T2.NAMES 
       ) RES
       ON T1.NAMES = RES.NAMES
       JOIN TABLE3_DATES T3
       ON T1.NAMES = T3.NAMES
);
COMMIT;

INSERT INTO RESULT2_FINAL 
(
  SELECT DISTINCT R1.NAMES, 'ABC' EX_CD
  FROM RESULT1_CALC R1
  WHERE R1.TOT_UNITS <> (EXTRACT(DAY FROM TO_DT - FR_DT))
);
COMMIT;

核心问题：

1. 未区分人员的CODE类型（仅含180/混合/无180）；
2. 仅判断了与TO_DT - FR_DT的对比，未覆盖规则2、3的判断逻辑。

预期结果 #

RESULT2_FINAL表应包含以下记录：

修正后的SQL #

方案：直接关联查询完成判断（无需依赖中间表） #

TRUNCATE TABLE RESULT2_FINAL; -- 按需清空表
INSERT INTO RESULT2_FINAL 
(
  SELECT DISTINCT T1.NAMES, 'ABC' AS EX_CD
  FROM TABLE1_NAMES T1
  JOIN TABLE3_DATES T3 ON T1.NAMES = T3.NAMES
  JOIN (
    SELECT T2.NAMES,
           SUM(T2.UNITS) AS TOT_UNITS,
           -- 标记是否包含180码
           MAX(CASE WHEN T2.CODE = '180' THEN 1 ELSE 0 END) HAS_180,
           -- 标记是否包含其他合格码
           MAX(CASE WHEN T2.CODE <> '180' THEN 1 ELSE 0 END) HAS_OTHER
    FROM TABLE2_LINES T2
    JOIN TABLE4_CODES T4
      ON T4.CD_NM = 'CODELIST'
      AND T2.CODE BETWEEN T4.B_CODE AND T4.E_CODE
    GROUP BY T2.NAMES
  ) RES ON T1.NAMES = RES.NAMES
  WHERE (
    -- 规则1：仅含180，对比TO_DT-FR_DT天数
    (RES.HAS_180 = 1 AND RES.HAS_OTHER = 0 AND RES.TOT_UNITS <> EXTRACT(DAY FROM T3.TO_DT - T3.FR_DT))
    OR
    -- 规则2+3：混合码或无180，对比END_DT-START_DT天数
    ((RES.HAS_180 = 1 AND RES.HAS_OTHER = 1) OR RES.HAS_180 = 0) 
    AND RES.TOT_UNITS <> EXTRACT(DAY FROM T3.END_DT - T3.START_DT)
  )
);
COMMIT;

修正说明 #

1. 在子查询中直接计算每个人员的CODE类型标记（HAS_180、HAS_OTHER），无需依赖中间表RESULT1_CALC；
2. WHERE条件完整覆盖了三个业务规则的判断逻辑；
3. 简化了数据流转，避免中间表的冗余存储，逻辑更清晰。

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