Oracle中按条件将TABLE1数据插入TABLE2的实现问题

现有TABLE1表结构及测试数据 #

CREATE TABLE TABLE1 (ROLL_NO VARCHAR2(3), NAME VARCHAR2(4), UNITS NUMBER, AMOUNT NUMBER, CODE VARCHAR2(3));
------------------------------------------------------------------------------------------
INSERT INTO TABLE1 VALUES ('101', 'JOHN', 1, 6, 'ABC');
INSERT INTO TABLE1 VALUES ('101', 'JOHN', 2, 6, 'ABC');
INSERT INTO TABLE1 VALUES ('102', 'TOMS', 1, 7, 'ABC');
INSERT INTO TABLE1 VALUES ('102', 'TOMS', 6, 7, 'ABC');
INSERT INTO TABLE1 VALUES ('103', 'FINN', 1, 1, 'BCD');

目标TABLE2表结构 #

CREATE TABLE TABLE2 (ROLL_NO VARCHAR2(3), NAME VARCHAR2(4), RESULT VARCHAR2(3));

数据插入规则 #

• 若CODE为'ABC'且对应ROLL_NO的SUM(UNITS)等于AMOUNT，则不插入该记录；
• 若CODE为'ABC'且对应ROLL_NO的SUM(UNITS)不等于AMOUNT，则插入记录且RESULT字段值为'YES'；
• 若CODE不为'ABC'，则插入记录且RESULT字段值为'YES'。

注：同一ROLL_NO对应的NAME、CODE、AMOUNT值一致。

预期TABLE2最终数据 #

ROLL_NO    NAME      RESULT
   -----------------------------
    101       JOHN       YES 
    103       FINN       YES

错误的尝试代码 #

原查询会错误插入ROLL_NO为102的记录，代码如下：

SELECT T1.ROLL_NO, T1.NAME,
        CASE 
           WHEN T1.CODE <> 'ABC' THEN 'YES'
           WHEN T1.CODE = 'ABC' AND T2.TOT_UNITS <> T1.AMOUNT THEN 'YES'
        END RESULT
 FROM (SELECT DISTINCT ROLL_NO, NAME, AMOUNT, CODE 
       FROM TABLE1 ) T1
 JOIN (SELECT ROLL_NO, SUM(UNITS) AS TOT_UNITS
       FROM TABLE1
       GROUP BY ROLL_NO) T2 ON T1.ROLL_NO = T2.ROLL_NO

正确实现方案 #

原查询的问题在于未过滤符合规则1的记录：当CODE='ABC'且SUM(UNITS)=AMOUNT时，查询仍会返回该记录（此时RESULT为NULL），导致错误插入。需要添加WHERE子句排除这类记录，同时简化RESULT的赋值逻辑。

以下是正确的插入SQL：

INSERT INTO TABLE2 (ROLL_NO, NAME, RESULT)
SELECT T1.ROLL_NO, T1.NAME, 'YES' AS RESULT
FROM (
    SELECT DISTINCT ROLL_NO, NAME, AMOUNT, CODE 
    FROM TABLE1
) T1
JOIN (
    SELECT ROLL_NO, SUM(UNITS) AS TOT_UNITS
    FROM TABLE1
    GROUP BY ROLL_NO
) T2 ON T1.ROLL_NO = T2.ROLL_NO
WHERE 
    -- 直接排除不需要插入的情况：CODE为ABC且总UNITS等于AMOUNT
    NOT (T1.CODE = 'ABC' AND T2.TOT_UNITS = T1.AMOUNT);

代码说明 #

1. 用NOT (T1.CODE = 'ABC' AND T2.TOT_UNITS = T1.AMOUNT)精准过滤掉不符合插入条件的记录；
2. 符合插入要求的记录RESULT统一为'YES'，无需复杂CASE判断，简化逻辑；
3. 保留原查询的子查询结构，确保获取每个ROLL_NO的唯一属性值和汇总数据。

执行以上SQL后，TABLE2的数据将与预期完全一致。

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