我想写查询添加列,计算值使用汇率从另一个表的转换率
第一个表:
ValueinlocalCCY02.06.2022 5000 600040000
这是一个简单的INNER JOIN和汇率应用程序,适用于两个平台。
SELECT data.Date,
data.localCCY,
data.ValueinlocalCCY / rates.USDFactor AS ValueInUSD
FROM Table1 AS data
INNER JOIN Table2 AS rates ON rates.Date = data.Date
AND rates.CCY = data.localCCY;
SQL Server示例:
DROP TABLE IF EXISTS #data;
CREATE TABLE #data (
[Date] DATE,
[localCCY] VARCHAR(100),
[ValueinlocalCCY] DECIMAL(18, 6)
);
DROP TABLE IF EXISTS #rates;
CREATE TABLE #rates (
[Date] DATE,
[CCY] VARCHAR(100),
[USDFactor] DECIMAL(18, 6)
);
INSERT INTO #data ([Date],[localCCY],[ValueinlocalCCY])
SELECT '02.06.2022', 'Eur', 5000 UNION ALL
SELECT '02.06.2022', 'Gbp', 6000 UNION ALL
SELECT '01.06.2022', 'CHF', 40000;
INSERT INTO #rates ([Date],[CCY],[USDFactor])
SELECT '02.06.2022', 'Eur', 0.99 UNION ALL
SELECT '02.06.2022', 'Gbp', 0.9 UNION ALL
SELECT '02.06.2022', 'CHF', 0.95 UNION ALL
SELECT '01.06.2022', 'Eur', 0.98 UNION ALL
SELECT '01.06.2022', 'Gbp', 0.91 UNION ALL
SELECT '01.06.2022', 'CHF', 0.92;
SELECT d.date, d.localCCY, d.ValueinlocalCCY / r.USDFactor AS [Value In USD]
FROM #data d
INNER JOIN #rates r ON r.Date = d.Date
AND r.CCY = d.localCCY;