如何在单行数据中复用子查询定义的@ConversionRate变量

你的需求是可行的，但不能用你当前的变量赋值方式——在SELECT语句中直接给变量赋值的行为不可靠，SQL Server不保证变量会逐行更新并在当前行的后续列中使用最新值，很可能出现所有行复用同一个汇率的错误情况。

正确的做法是用CROSS APPLY先为每行计算出对应的汇率，再在查询中直接复用这个计算好的值，既保证单行内汇率固定，又能让每行使用自己对应的汇率，效率也不受影响。

修正后的完整代码 #

DECLARE @CurrencyType VARCHAR(10) = 'USD/GBP'

-- 创建汇率表
CREATE TABLE CurrencyRate (
    CURCODE VARCHAR(8),
    CURDATE VARCHAR(8),
    CURRATE DECIMAL(18,6)
)

-- 插入汇率数据（修正原数据列顺序错误）
INSERT INTO CurrencyRate (CURCODE, CURDATE, CURRATE)
VALUES
    ('USD/AED', '20231001', 3.672775),
    ('USD/GBP', '20231001', 0.81671),
    ('USD/AED', '20231101', 3.672435),
    ('USD/GBP', '20231101', 0.80671)

-- 创建乘客票价表
CREATE TABLE PassengerFare (
   ID INT,
   CreateDate DATE,
   PName NVARCHAR(50),
   [Desc] NVARCHAR(50),
   FareTotal DECIMAL(18,2),
   FareDiscount DECIMAL(18,2)
)

-- 插入乘客票价数据
INSERT INTO PassengerFare (ID, CreateDate, PName, [Desc], FareTotal, FareDiscount)
VALUES
    (1, '2023-09-14', 'Mark P.', 'OneWay', 178.75, 15.00),
    (2, '2023-10-11', 'Jeff H.', 'TwoWay', 319.50, 30.00)

-- 核心查询：用CROSS APPLY每行计算一次汇率，然后复用
SELECT
    pf.PName,
    pf.CreateDate,
    pf.FareTotal * cr.CURRATE AS ConvertedFareTotal,
    pf.FareDiscount * cr.CURRATE AS ConvertedFareDiscount
FROM PassengerFare pf
CROSS APPLY (
    -- 为当前行的乘客日期匹配最新的有效汇率
    SELECT TOP 1 c.CURRATE
    FROM CurrencyRate c WITH (NOLOCK)
    WHERE c.CURCODE = @CurrencyType 
      AND c.CURDATE <= CONVERT(VARCHAR, pf.CreateDate, 112)
    ORDER BY c.CURDATE DESC
) cr

-- 清理临时表
DROP TABLE IF EXISTS PassengerFare
DROP TABLE IF EXISTS CurrencyRate

关键说明 #

• CROSS APPLY会为PassengerFare的每一行执行一次子查询，计算出该行对应的汇率并存储在cr结果集中
• 同一行的ConvertedFareTotal和ConvertedFareDiscount直接复用cr.CURRATE，避免重复计算
• 这种方式既保证了每行汇率的正确性，又实现了单行内复用计算结果的需求，执行效率稳定可靠

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