我有TABLE_A并且TABLE_B我需要创建TABLE_B_FINAL。
规则：
如果在TABLE_A的值ID_C=1和的值中，AB=AFTER我们将的值乘以（行和列）中的WEIGHT对应值。下面的例子：如果在的值和的值，我们将相乘的值与对应的值（行和列）。如果在价值和价值，我们将成倍的价值与相应的值（行和列）。该算法检查中的所有记录TABLE_BID_C=1AFTERTABLE_AID_C=1AB=BEFOREWEIGHTTABLE_BID_C=1BEFORETABLE_AID_C=2AB=AFTERWEIGHTTABLE_BID_C=2AFTERTABLE_A并且将其值WEIGHT与对应的值相乘TABLE_B。
开头的所有行和列（和）都TABLE_B具有默认值。该值将在乘法过程中更改。1AFTERBEFORE
TABLE_A
ID_N | WEIGHT_N | ID_C | AB | 5 | 1.15 | 1 |之后| 12 | 1.13 | 1 |之前| 60 | 1.65 | 3 |之后| 90 | 1.85 | 2 |之后| 80 | 1.45 | 3 |之后| 140 | 1.57 | 2 |之前|
TABLE_B
| ID_C |之前|之后| | 1 | 1 | 1 | | 2 | 1 | 1 | | 3 | 1 | 1 |
TABLE_B_FINAL
| ID_C |之前|之后| | 1 | 1 * 1.13 = 1.13 | 1 * 1.15 = 1.15 | | 2 | 1,57 | 1,85 | | 3 | 1 | 1 * 1.65 * 1.45 = 2.39 |
Select TABLE_B.ID_C AS ID_C, BEFORE =
CASE TABLE_C.AB
WHEN 'BEFORE' THEN TABLE_B.BEFORE*TABLE_C.WEIGHT_N
ELSE TABLE_B.BEFORE
END,
AFTER =
CASE TABLE_C.AB
WHEN 'AFTER' THEN TABLE_B.AFTER*TABLE_C.WEIGHT_N
ELSE TABLE_B.AFTER
END
FROM (SELECT ID_C, AB, EXP(SUM(ln(WEIGHT_N))) FROM TABLE_A GROUP BY ID_C, AB) as TABLE_C
JOIN TABLE_B ON TABLE_B.ID_C=TABLE_C.ID_C;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句