#oracle #oracle-sqldeveloper
Вопрос:
У меня есть таблица с именем ТАБЛИЦА1, содержащая 4 столбца (ИДЕНТИФИКАТОР, СТРОКА, СТОИМОСТЬ, КОД)
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
Из приведенной выше таблицы вы можете видеть, что я взял сумму стоимости каждого идентификатора, соответствующую каждой СТРОКЕ…
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
У меня есть ТАБЛИЦА2, содержащая 6 столбцов (ИДЕНТИФИКАТОР,СТРОКА, 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
У меня есть ТАБЛИЦА 3 с 2 столбцами (СЕК, ПИН)
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
The above resultant logic is
Case1:
From TABLE1, For ID = 101 amp; 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 amp; 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 amp; 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 amp; 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
Once this logic over, we will be using INNER JOIN with TABLE3 two times(once to populate PR_PIN values from TABLE3 in resultant table and second time to populate SC_PIN values from TABLE3 in resultant table)
The resultant table be like
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-й случай работает так, как ожидалось, но остальные три случая частично верны, но не совсем верны…Может ли кто-нибудь помочь мне в этом
Комментарии:
1. Может ли кто-нибудь заняться этой проблемой