如何将临时表每行值传入标量函数并求和转换为单一货币

我明白你的需求啦——你需要把每种货币的总金额通过标量转换函数转成目标货币，最后算出总和。这里有两种简洁的实现方式：

方式一：直接聚合计算（推荐） #

不需要先插入临时表，一步到位完成计算，效率更高：

-- 先定义你要转换到的目标货币，比如这里是EU
DECLARE @targetCurrencyId CHAR(3) = 'EU'

SELECT 
    SUM([dbo].[function_scalar](Amount, CurrencyId, @targetCurrencyId)) AS TotalConvertedAmount
FROM (
    -- 先按货币分组算出每种货币的总金额
    SELECT SUM(Amount) AS Amount, CurrencyId 
    FROM [dbo].[fn_DepositWithdrawReport]() 
    WHERE OperationTypeId = 2 
    GROUP BY CurrencyId
) AS CurrencyTotals

方式二：保留临时表的写法 #

如果一定要保留你原来的临时表逻辑，可以这样修改：

DECLARE @targetCurrencyId CHAR(3) = 'EU'
DECLARE @GetTotalPaymentAmount AS TABLE ( Amount DECIMAL(18,4), CurrencyId CHAR(3) )

INSERT INTO @GetTotalPaymentAmount
SELECT SUM(Amount),CurrencyId FROM [dbo].[fn_DepositWithdrawReport]() WHERE OperationTypeId = 2 GROUP BY CurrencyId

-- 对临时表的每一行调用转换函数，再求和
SELECT SUM([dbo].[function_scalar](Amount, CurrencyId, @targetCurrencyId)) AS TotalConvertedAmount
FROM @GetTotalPaymentAmount

小提醒 #

注意你例子里标量函数的拼写有笔误（functin_scalar），实际要和你数据库里的函数名function_scalar保持一致哦~

内容的提问来源于stack exchange，提问作者So_oP