多次将两行差值转为列并关联Header表的SQL查询求助

嗨，完全懂这种好久不用语法就卡壳的感觉！别慌，咱们一步步把这个数据整理问题解决掉～

首先得先明确表结构，我先假设一个通用的结构来举例，你可以根据实际情况调整：

• Header表：HeaderID（主键），还有你需要保留的业务字段，比如OrderDate、CustomerID
• Details表：DetailID（主键），HeaderID（关联Header的外键），GroupID（标记每组，比如1/2/3），RecordType（标记每组里的两条记录，比如'初始值'和'最终值'），以及需要计算差值的列，比如Amount、Quantity、Price

我们可以把Details表自己和自己连接，把每组内的两条记录匹配上，算出差值后再关联回Header表：

SELECT
    h.HeaderID,
    h.OrderDate,
    h.CustomerID,
    -- 这里用最终值减初始值，你可以根据需求调整顺序
    d_final.GroupID,
    d_final.Amount - d_initial.Amount AS Amount_Diff,
    d_final.Quantity - d_initial.Quantity AS Quantity_Diff,
    d_final.Price - d_initial.Price AS Price_Diff
FROM
    Header h
JOIN
    Details d_final ON h.HeaderID = d_final.HeaderID
JOIN
    Details d_initial ON h.HeaderID = d_initial.HeaderID
                    AND d_final.GroupID = d_initial.GroupID
                    AND d_final.RecordType = '最终值' -- 替换成你实际的后记录标识
                    AND d_initial.RecordType = '初始值' -- 替换成你实际的前记录标识
ORDER BY
    h.HeaderID,
    d_final.GroupID;

如果你的Details表没有专门的字段标记每组内的两条记录（比如只有GroupID，两条记录没有区分标识），可以用ROW_NUMBER()给每组内的记录编个号，再匹配计算：

WITH NumberedDetails AS (
    SELECT
        HeaderID,
        GroupID,
        Amount,
        Quantity,
        Price,
        -- 给每组内的记录按ID排序编号，你也可以换成其他排序字段
        ROW_NUMBER() OVER (PARTITION BY HeaderID, GroupID ORDER BY DetailID) AS RowNum
    FROM
        Details
)
SELECT
    h.HeaderID,
    h.OrderDate,
    h.CustomerID,
    nd2.GroupID,
    nd2.Amount - nd1.Amount AS Amount_Diff,
    nd2.Quantity - nd1.Quantity AS Quantity_Diff,
    nd2.Price - nd1.Price AS Price_Diff
FROM
    Header h
JOIN
    NumberedDetails nd2 ON h.HeaderID = nd2.HeaderID AND nd2.RowNum = 2
JOIN
    NumberedDetails nd1 ON h.HeaderID = nd1.HeaderID 
                        AND nd2.GroupID = nd1.GroupID 
                        AND nd1.RowNum = 1
ORDER BY
    h.HeaderID,
    nd2.GroupID;

如果需要把3组的差值都放在同一行（而不是每行显示一组），可以用CASE配合分组来实现：

WITH DiffCTE AS (
    -- 先算出所有组的差值
    SELECT
        h.HeaderID,
        d_final.GroupID,
        d_final.Amount - d_initial.Amount AS Amount_Diff,
        d_final.Quantity - d_initial.Quantity AS Quantity_Diff
    FROM
        Header h
    JOIN Details d_final ON h.HeaderID = d_final.HeaderID
    JOIN Details d_initial ON h.HeaderID = d_initial.HeaderID
                        AND d_final.GroupID = d_initial.GroupID
                        AND d_final.RecordType = '最终值'
                        AND d_initial.RecordType = '初始值'
)
SELECT
    HeaderID,
    -- 提取每组的差值列
    MAX(CASE WHEN GroupID = 1 THEN Amount_Diff END) AS Group1_Amount_Diff,
    MAX(CASE WHEN GroupID = 1 THEN Quantity_Diff END) AS Group1_Quantity_Diff,
    MAX(CASE WHEN GroupID = 2 THEN Amount_Diff END) AS Group2_Amount_Diff,
    MAX(CASE WHEN GroupID = 2 THEN Quantity_Diff END) AS Group2_Quantity_Diff,
    MAX(CASE WHEN GroupID = 3 THEN Amount_Diff END) AS Group3_Amount_Diff,
    MAX(CASE WHEN GroupID = 3 THEN Quantity_Diff END) AS Group3_Quantity_Diff
FROM
    DiffCTE
GROUP BY
    HeaderID;

要是你的实际表结构和我假设的不一样（比如分组字段名称、数值列不同），可以把你的表结构贴出来，我再帮你调整语句！

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