SQL如何将不同日期价格数据合并为单行两列实现交易价格对比

解决方案 #

你可以直接通过两次左关联Prices表实现需求，一次关联匹配交易当日的理论价，一次关联匹配交易前一日的理论价，无需聚合或行转列操作，写法如下：

SELECT 
    T.Transaction_no,
    T.ID_no,
    T.Price_Traded,
    T.Trade_Date,
    P_TD.Price_Theo AS Price_Theo_TD,
    P_TD1.Price_Theo AS [Price_Theo TD-1]
FROM Transactions T
-- 关联当日理论价
LEFT JOIN Prices P_TD 
    ON T.ID_no = P_TD.ID_no 
    AND P_TD.Date = T.Trade_Date
-- 关联前一日理论价
LEFT JOIN Prices P_TD1 
    ON T.ID_no = P_TD1.ID_no 
    AND P_TD1.Date = T.Trade_Date - INTERVAL '1' DAY
WHERE T.Transaction_no IN ('T1', 'T2')

说明 #

• 你之前的查询返回两行是因为单次关联匹配到了两日的价格记录，拆分两次关联即可把两个价格映射到不同列
• 用左关联可以兼容部分交易不存在对应日期理论价的场景，缺失的价格会返回NULL
• 如果你的数据库环境不支持INTERVAL写法，可以替换为对应数据库的日期计算语法，比如SQL Server中可以写成DATEADD(DAY, -1, T.Trade_Date)

如果你确实想用PIVOT实现也可以，虽然需要用到聚合函数，但因为同一个ID、同一个日期只会对应一条理论价，用MAX/MIN聚合不会改变原始值，参考写法：

SELECT 
    Transaction_no,
    ID_no,
    Price_Traded,
    Trade_Date,
    [0] AS Price_Theo_TD,
    [1] AS [Price_Theo TD-1]
FROM (
    SELECT 
        T.Transaction_no,
        T.ID_no,
        T.Price_Traded,
        T.Trade_Date,
        P.Price_Theo,
        DATEDIFF(DAY, P.Date, T.Trade_Date) AS Day_Diff
    FROM Transactions T
    JOIN Prices P ON T.ID_no = P.ID_no
    WHERE T.Transaction_no IN ('T1', 'T2')
        AND DATEDIFF(DAY, P.Date, T.Trade_Date) IN (0,1)
) AS Source
PIVOT (
    MAX(Price_Theo)
    FOR Day_Diff IN ([0],[1])
) AS PivotTable

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