我有包含货币汇率和另一个转换金额的表。
货币表此表将包含货币的每日汇率。为了便于映射，我只包含了一个日期的汇率。
Branch Code Rate Date
A 0 1 30/10/2019
A 1 200 30/10/2019
A 2 300 30/10/2019
B 0 1 30/10/2019
B 1 10 30/10/2019
B 2 30 30/10/2019
为了便于映射,我只包括了一个日期的交易记录。
Branch Code Amt Date
A 0 1000 30/10/2019
A 1 2000 30/10/2019
A 2 3000 30/10/2019
A 2 4000 30/10/2019
A 2 5000 30/10/2019
A 0 6000 30/10/2019
B 0 7000 30/10/2019
B 0 8000 30/10/2019
B 0 9000 30/10/2019
B 2 10000 30/10/2019
结果表
Branch Code Date Amt Branch Code Rate Total
A 0 30/10/2019 1000 A 0 1 1000
A 1 30/10/2019 2000 A 1 200 400000
A 2 30/10/2019 3000 A 2 300 900000
A 2 30/10/2019 4000 A 2 300 1200000
A 2 30/10/2019 5000 A 2 300 1500000
A 0 30/10/2019 6000 A 1 1 6000
B 0 30/10/2019 7000 A 1 200 1400000
B 0 30/10/2019 8000 A 1 200 1600000
B 0 30/10/2019 9000 A 1 200 1800000
B 2 30/10/2019 10000 A 2 300 3000000
我只能得到下面的结果表。
Branch Code Date Amt Branch Code Rate Total
A 0 30/10/2019 1000 A 0 1 1000
A 1 30/10/2019 2000 A 1 200 400000
A 2 30/10/2019 3000 A 2 300 900000
A 2 30/10/2019 4000 A 2 300 1200000
A 2 30/10/2019 5000 A 2 300 1500000
A 0 30/10/2019 6000 A 1 1 6000
B 0 30/10/2019 7000 A 1 1 7000
B 0 30/10/2019 8000 A 1 1 8000
B 0 30/10/2019 9000 A 1 1 9000
B 2 30/10/2019 10000 A 2 300 3000000
我使用的查询。
SELECT
*
FROM
converted_amounts_table t1
LEFT OUTER JOIN (
SELECT
(
CASE WHEN code = '0'
AND branch = 'B' THEN '1' ELSE code END
) AS new_code,
branch,
date,
rate
FROM
currency_table
) t2 ON (
t1.date = t2.date
AND t1.code = t2.new_code
)
WHERE
t1.date >= '01-Jan-2019'
AND t1.date <= '30-Sep-2019'
0
我能够得到结果表。
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=4d5401320bda5ad736070541d55d1d04SELECT
t1.branch,
t1.date2,
t1.code,
t2.code,
t2.new_code,
t2.rate,
t1.amt,
t1.amt * t2.rate AS Total
FROM
converted_amounts_table t1
LEFT OUTER JOIN (
SELECT
(CASE WHEN code = 1 THEN 0 ELSE code END) AS new_code,
branch,
date1,
code,
rate
FROM
currency_table
WHERE
branch = 'A'
) t2 ON (
t1.date2 = t2.date1
AND t1.code IN (t2.new_code, t2.code)
)
WHERE
t1.date2 = '30 - Oct - 2019'
AND (
t1.branch, t1.code, t2.code, t2.new_code,
t2.rate
) NOT IN (
('B', 0, 0, 0, 1),
('A', 0, 1, 0, 200)
)
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
![cv人生履历：从零开始，一步步走向成功——[姓名]的职业生涯](/titimg/mwenw_article_thumb_32902.jpg)
评论列表（18条）