将记录从一个表插入到另一个表- Oracle

Question

我有一个表TABLE1，它有5列(ROLL_NO, NAME, UNITS, CODE, AMOUNT);

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');
ROLL_NO     NAME    UNITS    AMOUNT    CODE
-------------------------------------------------------
101       JOHN     1         6        ABC
101       JOHN     2         6        ABC
-------------------------------------------
102       TOMS     1         7        ABC
102       TOMS     6         7        ABC

---

103       FINN     1         1        BCD

有第二个表TABLE2，我们需要从TABLE1

中插入数据

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

在表2

中插入数据有三个条件

1st case : If CODE is 'ABC' and SUM(UNITS) of particular ROLL_NO is equal to AMOUNT then don't insert data into TABLE2
2nd case : If CODE is 'ABC' and SUM(UNITS) of particular ROLL_NO is not equal to AMOUNT then insert data with RESULT column value as 'YES'
3rd case : If CODE is not 'ABC' then RESULT column will be 'YES'.

注意:NAME, CODE和AMOUNT对于特定的ROLL_NO是相同的，尽管ROLL_NO有多个单位。

的例子:

ROLL_NO 102 CODE 'ABC' and two lines with SUM(UNITS) as 7 and its equal to AMOUNT i.e. 7 and  (1st case)     
ROLL_NO 101 has CODE 'ABC' and two lines with SUM(UNITS) as 3 and its not equal to AMOUNT i.e. 6   (2nd case) 
ROLL_NO 103 has CODE 'BCD' which is not equal to 'ABC'(3rd case) 

在TABLE2的末尾应该有

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

我已经尝试过这个oracle查询，但它正在插入与102 ROLL_NO相关的数据，我不需要

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

我不知道如何不插入roll_no102记录到TABLE2..如果可能的话，有人能提供比这更好的查询吗?谢谢你

Answer 1

A "better"选项是只扫描table1一次。

SQL> insert into table2 (roll_no, name, result)
2  with temp as
3    (select roll_no, name, sum(units) sum_units, amount, code,
4       case when code = 'ABC' and sum(units) = amount then 'NO'
5            when code = 'ABC' and sum(units) <> amount then 'YES'
6            else 'YES'
7       end as result
8     from table1
9     group by roll_no, name, amount, code
10    )
11  select roll_no, name, result
12  from temp
13  where result = 'YES';
2 rows created.
SQL> select * from table2;
ROL NAME RES
--- ---- ---
101 JOHN YES
103 FINN YES
SQL>