代码之家 › 专栏 › 技术社区 › Serge Kashlik

如何在SQL中计算并添加新列

calculated-columns multiplication sql-server

Serge Kashlik · 技术社区 · 7 年前

我有两张桌子

[表1]-特定货币的金额。

Currency  | Amount  
USD       |  243  
USD       |  212  
USD       |  432   
EUR       |  578  
EUR       |  112  
EUR       |  578  
GBP       |  965  
GBP       |  309

和[表2]-欧元和英镑对美元的汇率

Cur |   FX  
GBP | 1.43  
EUR | 1.18  
USD | 1

我需要在[表1]中永久添加一个新列[美元金额],将所有货币转换为美元。基本上,我需要将相应的货币乘以[表2]中给出的汇率。最终结果应该是这样的。

Currency  | Amount  |Amount in USD  
USD       |   243   |   243  
USD       |   212   |   212  
USD       |   432   |   432  
EUR       |   578   |   682  
EUR       |   112   |   132  
EUR       |   578   |   682  
GBP       |   965   |   1380  
GBP       |   309   |   442

如果有人能帮忙,我将不胜感激。提前谢谢。

3 回复 | 直到 7 年前

Thom A 7 年前

为您提供两种解决方案。一个使用 VIEW ,另一个是通过标量函数计算的列(这是您所说的您想要的):

--Create sample data
CREATE TABLE ExchangeRate (Currency char(3), FX decimal(8,2));
INSERT INTO ExchangeRate
VALUES ('GBP',1.43),
       ('EUR',1.18),
       ('USD',1);
GO

CREATE TABLE TransactionList (Currency char(3), Amount decimal(12,2));

INSERT INTO TransactionList
VALUES ('GBP',100),
       ('EUR', 100),
       ('USD',100);
GO
--View Solution
CREATE VIEW ValuesInUSD AS

    SELECT TL.Currency, TL.Amount, TL.Amount * ER.FX AS USDAmount
    FROM TransactionList TL
         JOIN ExchangeRate ER ON TL.Currency = ER.Currency;
GO
--Check results
SELECT *
FROM ValuesInUSD;

GO
--Function Solution
--Create Function
CREATE FUNCTION ExchangeToUSD (@Currency char(3), @Value decimal(12,2))
RETURNS DECIMAL(12,2)
AS BEGIN
    DECLARE @USD decimal(12,2);

    SELECT @USD =  @Value * FX
    FROM ExchangeRate
    WHERE Currency = @Currency;

    RETURN @USD;

END
GO
--Add Computed Column
ALTER TABLE TransactionList ADD ValueInUSD AS (dbo.ExchangeToUSD(Currency, Amount));
GO
--Check results
SELECT *
FROM TransactionList;
GO
--Clean up
DROP VIEW ValuesInUSD;
DROP TABLE TransactionList;
DROP FUNCTION ExchangeToUSD;
DROP TABLE ExchangeRate;
GO

Sreenu131 7 年前

尝试这种方法

SELECT 
    Currency
    ,Amount
    ,CEILING(ROUND((Amount*FX),1)) AS [Amount in USD]
FROM Table_1 c1 
INNER JOIN Table_2 c2
ON c2.Cur=c1.Currency

后果

Currency    Amount  Amount in USD
-----------------------------------
USD          243       243
USD          212       212
USD          432       432
EUR          578       682
EUR          112       133
EUR          578       682
GBP          965       1380
GBP          309       442

Alfaiz Ahmed 7 年前

试试这个

CREATE  TABLE #Currency (Currency NVARCHAR(10),AMOUNT INT)
CREATE TABLE #Exchange_Rate (Currency NVARCHAR(10),FX DECIMAL(15,2))

INSERT INTO #Exchange_Rate
SELECT 'GBP', 1.43    UNION ALL
SELECT 'EUR', 1.18     UNION ALL
SELECT 'USD' , 1

INSERT INTO #Currency
SELECT 'USD' ,243   UNION ALL
SELECT 'USD',212    UNION ALL
SELECT 'USD', 432   UNION ALL
SELECT 'EUR',578    UNION ALL
SELECT 'EUR',112    UNION ALL
SELECT 'EUR', 578   UNION ALL
SELECT 'GBP', 965   UNION ALL
SELECT 'GBP',309

SELECT * FROM #Exchange_Rate

SELECT a.Currency,AMOUNT,a.AMOUNT * b.FX [Amount IN USD] 
FROM #Currency a INNER JOIN #Exchange_Rate b ON a.Currency=b.Currency