有表结构如下:
WITH t AS (
SELECT 'GA' goods_cd,'JPN' Currency,'1' PRICE, '100' Qty ,'20110101' DTE FROM dual UNION ALL
SELECT 'GB' goods_cd,'USD' Currency,'2' PRICE, '150' Qty ,'20111031' DTE FROM dual UNION ALL
SELECT 'GA' goods_cd,'JPN' Currency,'1' PRICE, '200' Qty ,'20100918' DTE FROM dual UNION ALL
SELECT 'GD' goods_cd,'USD' Currency,'4' PRICE, '300' Qty ,'20091223' DTE FROM dual UNION ALL
SELECT 'GC' goods_cd,'JPN' Currency,'7' PRICE, '250' Qty ,'20110621' DTE FROM dual UNION ALL
SELECT 'GD' goods_cd,'JPN' Currency,'5' PRICE, '400' Qty ,'20111017' DTE FROM dual UNION ALL
SELECT 'GA' goods_cd,'USD' Currency,'3' PRICE, '600' Qty ,'20111019' DTE FROM dual
)
, tb AS (
SELECT 'JPN' Currency,'20110101' Begin_DTE,'20110125' END_DTE,0.0812 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100801' Begin_DTE,'20110823' END_DTE,0.0820 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100824' Begin_DTE,'20100923' END_DTE,0.0794 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20110525' Begin_DTE,'20110621' END_DTE,0.0823 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20110622' Begin_DTE,'20111016' END_DTE,0.0809 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20111017' Begin_DTE,'20111031' END_DTE,0.0784 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20111029' Begin_DTE,'20111123' END_DTE,6.75 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20091128' Begin_DTE,'20091231' END_DTE,7.21 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20110928' Begin_DTE,'20111028' END_DTE,6.81 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20080101' Begin_DTE,'20080131' END_DTE,6.67 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20100209' Begin_DTE,'20100223' END_DTE,7.11 RATE FROM dual
)
现在要将t表中的price折算成人民币金额。
例如:20110101年GA的价格为1(JPN),当时的汇率为:0.0812
要求得的价格为:1*0.0812 总金额为:100*1*0.0812结果格式为:
GOODS_CD DTE 币种 汇率 数量 折算前价格 折算后价格 折算前总金额 折算后总金额谢谢~~~
WITH t AS (
SELECT 'GA' goods_cd,'JPN' Currency,'1' PRICE, '100' Qty ,'20110101' DTE FROM dual UNION ALL
SELECT 'GB' goods_cd,'USD' Currency,'2' PRICE, '150' Qty ,'20111031' DTE FROM dual UNION ALL
SELECT 'GA' goods_cd,'JPN' Currency,'1' PRICE, '200' Qty ,'20100918' DTE FROM dual UNION ALL
SELECT 'GD' goods_cd,'USD' Currency,'4' PRICE, '300' Qty ,'20091223' DTE FROM dual UNION ALL
SELECT 'GC' goods_cd,'JPN' Currency,'7' PRICE, '250' Qty ,'20110621' DTE FROM dual UNION ALL
SELECT 'GD' goods_cd,'JPN' Currency,'5' PRICE, '400' Qty ,'20111017' DTE FROM dual UNION ALL
SELECT 'GA' goods_cd,'USD' Currency,'3' PRICE, '600' Qty ,'20111019' DTE FROM dual
)
, tb AS (
SELECT 'JPN' Currency,'20110101' Begin_DTE,'20110125' END_DTE,0.0812 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100801' Begin_DTE,'20110823' END_DTE,0.0820 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100824' Begin_DTE,'20100923' END_DTE,0.0794 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20110525' Begin_DTE,'20110621' END_DTE,0.0823 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20110622' Begin_DTE,'20111016' END_DTE,0.0809 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20111017' Begin_DTE,'20111031' END_DTE,0.0784 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20111029' Begin_DTE,'20111123' END_DTE,6.75 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20091128' Begin_DTE,'20091231' END_DTE,7.21 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20110928' Begin_DTE,'20111028' END_DTE,6.81 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20080101' Begin_DTE,'20080131' END_DTE,6.67 RATE FROM dual UNION ALL
SELECT 'USD' Currency,'20100209' Begin_DTE,'20100223' END_DTE,7.11 RATE FROM dual
)
现在要将t表中的price折算成人民币金额。
例如:20110101年GA的价格为1(JPN),当时的汇率为:0.0812
要求得的价格为:1*0.0812 总金额为:100*1*0.0812结果格式为:
GOODS_CD DTE 币种 汇率 数量 折算前价格 折算后价格 折算前总金额 折算后总金额谢谢~~~
你看t表的dte=20110101, 这个时间的汇率,在表tb里对应的数据有2条,就是前2条都符合:
SELECT 'JPN'Currency,'20110101' Begin_DTE,'20110125' END_DTE,0.0812 RATE FROM dual
SELECT 'JPN' Currency,'20100801' Begin_DTE,'20110823' END_DTE,0.0820 RATE FROM dual
这时候取哪个汇率呢?取0.0812 还是0.0820 ?
===
如果不存在上述数据问题的话,这样写就行了:
select a.goods_cd, a.dte, a.currency, b.rate, a.qty, a.price, a.price*b.rate,
a.price*a.qty, a.price*a.qty*b.rate
from t a, tb b
where a.currency = b.currency and a.dte between b.begin_dte and b.end_dte;
SELECT T.GOODS_CD,T.DTE,T.Currency,TB.RATE,T.Qty,T.PRICE,T.PRICE*TB.RATE PRICE1,T.PRICE*T.Qty TOTLE,T.PRICE*TB.RATE*T.Qty TOTLE1
FROM T,TB
WHERE T.Currency=TB.Currency AND
T.DTE >= TB.Begin_DTE
AND T.DTE < TB.END_DTE;GOODS_CD DTE CURRENCY RATE QTY PRICE PRICE1 TOTLE TOTLE1
-------- -------- -------- ---------- --- ----- ---------- ---------- ----------
GA 20110101 JPN 0.0812 100 1 0.0812 100 8.12
GC 20110621 JPN 0.082 250 7 0.574 1750 143.5
GA 20100918 JPN 0.082 200 1 0.082 200 16.4
GA 20110101 JPN 0.082 100 1 0.082 100 8.2
GA 20100918 JPN 0.0794 200 1 0.0794 200 15.88
GD 20111017 JPN 0.0784 400 5 0.392 2000 156.8
GB 20111031 USD 6.75 150 2 13.5 300 2025
GD 20091223 USD 7.21 300 4 28.84 1200 8652
GA 20111019 USD 6.81 600 3 20.43 1800 12258--LZ你这2条记录的日期区间重复了,取出来的值就多了
SELECT 'JPN' Currency,'20110101' Begin_DTE,'20110125' END_DTE,0.0812 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100801' Begin_DTE,'20110823' END_DTE,0.0820 RATE FROM dual UNION ALL
--你的数据有问题呀,汇率区间有重复的。
----比如下面两个汇率就有重复,改正这个问题后,用下面的sql
SELECT 'JPN' Currency,'20110101' Begin_DTE,'20110125' END_DTE,0.0812 RATE FROM dual UNION ALL
SELECT 'JPN' Currency,'20100801' Begin_DTE,'20110823' END_DTE,0.0820 RATE FROM dual UNION ALLSELECT T.GOODS_CD,
T.DTE,
T.CURRENCY,
TB.RATE,
T.QTY,
T.PRICE,
T.PRICE * TB.RATE,
T.QTY * T.PRICE,
T.QTY * T.PRICE * TB.RATE
FROM T, TB
WHERE T.DTE >= TB.BEGIN_DTE
AND T.DTE < TB.END_DTE
AND T.CURRENCY = TB.CURRENCY
;
SELECT T.GOODS_CD,T.DTE,T.Currency,TB.RATE,T.Qty,T.PRICE,T.PRICE*TB.RATE PRICE1,T.PRICE*T.Qty TOTLE,T.PRICE*TB.RATE*T.Qty TOTLE1
FROM T,TB
WHERE T.Currency=TB.Currency AND
T.DTE >= TB.Begin_DTE
--少了个等号
AND T.DTE <= TB.END_DTE;