A表 curcode Home Value Dual Value x_Rate
CNY 1,354.00 193.05 7.0137
CNY 296.00 42.20 7.0137
CNY 1,650.00 235.25 7.0137
USD 9,496.55 1,354.00 7.0137
USD 2,076.06 296.00 7.0137
USD 11,572.61 1,650.00 7.0137想要实现下面的格式:
curcode Home Value Dual Value Home Exchange Rate Dual Exchange Rate
CNY 1,354.00 193.05 7.0137
CNY 296.00 42.20 7.0137
CNY 1,650.00 235.25 7.0137
USD 9,496.55 1,354.00 7.0137
USD 2,076.06 296.00 7.0137
USD 11,572.61 1,650.00 7.0137 规则:如果发票货币代码是CNY,那么取发票的数值(home value),dual value=home value/x_rate(汇率),并且将这个汇率添在dual exchange rate列
USD, 取发票数值(dual value), home value=dual value*x_rate,并且将这个汇率添在home exchange rate列
CNY 1,354.00 193.05 7.0137
CNY 296.00 42.20 7.0137
CNY 1,650.00 235.25 7.0137
USD 9,496.55 1,354.00 7.0137
USD 2,076.06 296.00 7.0137
USD 11,572.61 1,650.00 7.0137想要实现下面的格式:
curcode Home Value Dual Value Home Exchange Rate Dual Exchange Rate
CNY 1,354.00 193.05 7.0137
CNY 296.00 42.20 7.0137
CNY 1,650.00 235.25 7.0137
USD 9,496.55 1,354.00 7.0137
USD 2,076.06 296.00 7.0137
USD 11,572.61 1,650.00 7.0137 规则:如果发票货币代码是CNY,那么取发票的数值(home value),dual value=home value/x_rate(汇率),并且将这个汇率添在dual exchange rate列
USD, 取发票数值(dual value), home value=dual value*x_rate,并且将这个汇率添在home exchange rate列
CNY 1,354.00 193.05 7.0137
上面六个字段,下面四个字段,怎么对应的?
create table #t
(
curcode char(3),
homevale numeric(13,2),
dualvalue numeric(13,2),
x_rate numeric(13,6)
)
insert into #t
select 'CNY', 1354.00, 193.05 ,7.0137 union all
select 'CNY', 296.00, 42.20, 7.0137 union all
select 'CNY', 1650.00, 235.25 ,7.0137 union all
select 'USD', 9496.55,1354.00, 7.0137 union all
select 'USD', 2076.06, 296.00, 7.0137 union all
select 'USD', 11572.61 ,1650.00, 7.0137select * from #t这个是已有的表