如何在SQL中获取物料成本最后及上一次变更的对应值

需求背景 #

我们有一张记录物料成本变更历史的表（假设表名为CostHistory），表结构和现有数据如下：

HistoryID ItemID Cost Date       User
---------------------------------------------
136551    2233   120  2017-06-06 Bren
154021    2233   125  2018-01-26 Admin
136552    2251   89   2017-06-06 Bren
154023    2251   95   2018-01-26 Admin
154026    2251   100  2018-01-29 Manager

需要生成一份报表，展示每个物料的上一次成本、最后一次变更的成本、成本差值、最后变更日期及操作人，期望输出如下：

ItemID Old Cost New Cost Difference Changed Date  User
--------------------------------------------------------------
2233   120      125      5           2018-01-26   Admin
2251   95       100      5           2018-01-29   Manager

SQL实现方案 #

这里用窗口函数ROW_NUMBER()来给每个物料的变更记录按日期排序，再通过自关联获取上一次的成本值，具体SQL语句如下：

WITH RankedCosts AS (
    SELECT 
        ItemID,
        Cost,
        Date,
        User,
        -- 按物料分组，变更日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY Date DESC) AS rn
    FROM CostHistory
)
SELECT 
    curr.ItemID,
    prev.Cost AS [Old Cost],
    curr.Cost AS [New Cost],
    curr.Cost - prev.Cost AS Difference,
    curr.Date AS [Changed Date],
    curr.User
FROM RankedCosts curr
-- 关联上一次的变更记录（排名比当前小1）
LEFT JOIN RankedCosts prev 
    ON curr.ItemID = prev.ItemID 
    AND curr.rn = prev.rn - 1
-- 只取每个物料的最新变更记录
WHERE curr.rn = 1;

方案说明 #

• CTE分组排名：通过ROW_NUMBER()给每个物料的变更记录按日期倒序编号，最新的变更记录会被标记为rn=1，上一次变更为rn=2，以此类推。
• 自关联取历史成本：将CTE自身关联，把最新记录和上一次记录绑定，就能直接拿到新旧成本值。
• 差值计算：用最新成本减去上一次成本得到差值，同时取出最新变更的日期和操作人。

这个方案适配大多数支持窗口函数的数据库（比如SQL Server、MySQL 8.0+、PostgreSQL等），如果是不支持窗口函数的旧版本数据库，也可以用子查询实现，但窗口函数的写法更简洁高效。

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