oracle - 选择结果表中显示的列 - Oracle（未按预期工作）

Question

我有一个表名 TABLE1 有 4 列（ID、LINE、COST、CODE）

  CREATE TABLE TABLE1 ( ID NUMBER(4),  LINE NUMBER(3), COST NUMBER(4), CODE VARCHAR2(3) );


  /*1st case:*/
  INSERT INTO TABLE1 VALUES(101, 1, 40, 'ABC' );
  INSERT INTO TABLE1 VALUES(101, 1, 50, 'DEF' );

  /*2nd case:*/
  INSERT INTO TABLE1 VALUES(102, 2, 30, 'CDE' );
  INSERT INTO TABLE1 VALUES(102, 2, 20, 'ECD' );

  /*3rd case:*/
  INSERT INTO TABLE1 VALUES(103, 3, 5, 'BCD' );
  INSERT INTO TABLE1 VALUES(103, 3, 5, 'BCD' );

  /*4th case:*/
  INSERT INTO TABLE1 VALUES(104, 4, 15, 'OPQ' );
  INSERT INTO TABLE1 VALUES(104, 4, 15, 'PQO' );


  ID     LINE   COST    CODE
  --------------------------
  101     1     40       ABC
  101     1     50       DEF
  102     2     30       CDE
  102     2     20       ECD
  103     3     05       BCD
  103     3     05       BCD
  104     4     15       OPQ
  104     4     15       PQO
  
 

从上表中可以看出，我对每条线路分别计算了每个 ID 的成本总和......

 For ID = 101 and LINE = 1 -> SUM(COST) = 40+50 = 90 
 For ID = 102 and LINE = 2 -> SUM(COST) = 30+20 = 50
 For ID = 103 and LINE = 3 -> SUM(COST) = 05+05 = 10 
 For ID = 104 and LINE = 4 -> SUM(COST) = 15+15 = 30 

---

我有一个 TABLE2 有 6 列（ID、LINE、COST_PR、PR_CODE、COST_SC、SC_CODE）

 CREATE TABLE TABLE2 ( ID NUMBER(4), LINE NUMBER(3), COST_PR NUMBER(4), PR_CODE VARCHAR2(3), COST_SC NUMBER(4), SC_CODE VARCHAR2(3) );


 INSERT INTO TABLE2 VALUES(101, 1, 90, 'DFE', 100, 'CBA');
 INSERT INTO TABLE2 VALUES(102, 2, 60, 'CDE', 50,  'EDC');
 INSERT INTO TABLE2 VALUES(103, 3, 10, 'BCD', 10,  'DEF');
 INSERT INTO TABLE2 VALUES(104, 4, 10, 'XYZ', 20,  'ZXY');


 ID    LINE    COST_PR    PR_CODE    COST_SC     SC_CODE
 --------------------------------------------------------
 101    1       90          DFE       100         CBA
 102    2       60          CDE        50         EDC
 103    3       10          BCD        10         DEF
 104    4       10          XYZ        20         ZXY

---

我有一个包含 2 列（SEC、PIN）的 TABLE3

 CREATE TABLE TABLE3( SEC VARCHAR2(3), PIN VARCHAR2(2) );

 INSERT INTO TABLE3 VALUES ('ABC', 'A1' );
 INSERT INTO TABLE3 VALUES ('DEF', 'A2' );
 INSERT INTO TABLE3 VALUES ('CDE', 'A3' );
 INSERT INTO TABLE3 VALUES ('ECD', 'A4' );
 INSERT INTO TABLE3 VALUES ('BCD', 'A5' );
 INSERT INTO TABLE3 VALUES ('OPQ', 'A7' );
 INSERT INTO TABLE3 VALUES ('PQO', 'A8' );
 INSERT INTO TABLE3 VALUES ('XYZ', 'B1' );
 INSERT INTO TABLE3 VALUES ('ZXY', 'B2' );
 INSERT INTO TABLE3 VALUES ('CBA', 'B3' );
 INSERT INTO TABLE3 VALUES ('EDC', 'B4' );
 INSERT INTO TABLE3 VALUES ('DFE', 'B5' );

 SEC      PIN
 -------------
 ABC      A1
 DEF      A2
 CDE      A3
 ECD      A4
 BCD      A5
 OPQ      A7
 PQO      A8
 XYZ      B1
 ZXY      B2
 CBA      B3
 EDC      B4
 DFE      B5

---

上面的结果逻辑是

 Case1: 
  From TABLE1, For ID = 101 & LINE = 1, SUM(COST) = 40+50 = 90
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_PR 
    so we need to 
 a) populate CODE values from TABLE1 and PR_CODE from TABLE2 in PR_CODE columns and 
 b) populate SC_CODE values from TABLE2 as it is in SC_CODE column 

 Case2: 
  From TABLE1, For ID = 102 & LINE = 2, SUM(COST) = 30+20 = 50
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_SC 
    so we need to 
   a) populate CODE values from TABLE1 and SC_CODE values from TABLE2 in SC_CODE columns and 
   b) populate PR_CODE values from TABLE2 as it is in PR_CODE columns

 Case3: 
     From TABLE1, For ID = 103 & LINE = 3, SUM(COST) = 10
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) = COST_PR = COST_SC 
    so we need to 
    a) populate CODE values from TABLE1 and PR_CODE values from TABLE2 in PR_CODE columns  and 
    b) populate CODE values from TABLE1 and SC_CODE values from TABLE2 in SC_CODE columns 
    
   Case4: 
     From TABLE1, For ID = 104 & LINE = 4, SUM(COST) = 30
     we need to compare this SUM(COST) with COST_PR and COST_SC of TABLE2,
    In this case, SUM(COST) <> COST_PR and  SUM(COST)<> COST_SC 
    so we need to 
    a) populate PR_CODE values from TABLE2 in PR_CODE columns  and 
    b) populate SC_CODE values from TABLE2 in SC_CODE columns of resultant table

---

一旦这个逻辑结束，我们将使用 INNER JOIN 和 TABLE3 两次（一次是在结果表中填充来自 TABLE3 的 PR_PIN 值，第二次是在结果表中填充来自 TABLE3 的 SC_PIN 值）

结果表就像

 ID   LINE      PR_CODE  PR_PIN    SC_CODE  SC_PIN  
---------------------------------------------------
 /*1st case*/
101    1         ABC       A1      CBA       B3
101    1         DEF       A2      CBA       B3
101    1         DFE       A2      CBA       B3
/*2nd case*/
102    2         CDE       A3      CDE       A3
102    2         CDE       A3      ECD       A4
102    2         CDE       A3      EDC       B4
/*3rd case*/
103    3         BCD       A5      BCD       A5
103    3         BCD       A5      DEF       A2 
/*4th case*/
104    4         XYZ       B1      ZXY       B2

这是我使用的查询，它没有按预期工作......有人可以帮助我吗：

  WITH tmp1 AS 
 (
     SELECT DISTINCT t.*, 
                   SUM(cost) OVER (PARTITION BY id, line) AS sum_code
     FROM table1 t 
  )
  SELECT t1.id, t1.line,
   CASE 
      WHEN t2.cost_pr = t1.sum_code THEN t1.code 
      ELSE t2.pr_code 
   END AS pr_code,
   CASE 
     WHEN t2.cost_pr = t1.sum_code THEN t31.pin 
     ELSE t32.pin
   END AS pr_pin,
   CASE 
      WHEN t2.cost_sc = t1.sum_code THEN t1.code 
      ELSE t2.sc_code 
   END AS sc_code,
   CASE 
     WHEN t2.cost_sc = t1.sum_code THEN t31.pin
     ELSE t33.pin 
   END AS sc_pin
 FROM tmp1 t1
 INNER JOIN table2 t2
 ON t1.id = t2.id AND t1.line = t2.line
 INNER JOIN table3 t31
 ON t1.code = t31.sec
 INNER JOIN table3 t32
 ON t2.pr_code = t32.sec 
INNER JOIN table3 t33
ON t2.sc_code = t33.sec; 

结果表中不允许重复。如果有拼写错误，请告诉我....通过我的查询，我看到第 4 种情况按预期工作，但其余三种情况部分正确但不完全正确...有人可以帮助我吗

标签： oracleoracle-sqldeveloper