如何在SQL Server中为当前及历史记录创建历史数据集

核心思路 #

使用SQL Server原生支持的LAG()窗口函数，按Name分组分区、按修改时间升序排序，直接取同主体上一条记录的状态，过滤无前置状态的最早记录即可得到预期结果。

实现代码 #

1. 测试数据准备（可跳过，直接适配你的业务表即可） #

-- 模拟业务表结构
CREATE TABLE #BusinessTable (
    Name NVARCHAR(50),
    Status NVARCHAR(20),
    [Modified Date] DATE
)

-- 插入示例数据
INSERT INTO #BusinessTable VALUES
('X', 'Fail', '2021-09-16'),
('X', 'Fail', '2021-09-28'),
('X', 'Done', '2021-10-02'),
('Y', 'Fail', '2021-09-30'),
('Y', 'Done', '2021-10-02')

2. 核心查询（兼容SQL Server 2012及以上版本） #

SELECT
    Name,
    Status AS [Current Status],
    LAG(Status) OVER (PARTITION BY Name ORDER BY [Modified Date] ASC) AS [Previous Status],
    [Modified Date]
FROM #BusinessTable
-- 过滤无前置状态的各主体最早一条记录
WHERE LAG(Status) OVER (PARTITION BY Name ORDER BY [Modified Date] ASC) IS NOT NULL
ORDER BY Name DESC, [Modified Date] DESC

3. 低版本兼容方案（SQL Server 2008及以下，不支持LAG函数） #

WITH RankedRecords AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY [Modified Date] ASC) AS RowNum
    FROM #BusinessTable
)
SELECT
    curr.Name,
    curr.Status AS [Current Status],
    prev.Status AS [Previous Status],
    curr.[Modified Date]
FROM RankedRecords curr
INNER JOIN RankedRecords prev 
    ON curr.Name = prev.Name 
    AND curr.RowNum = prev.RowNum + 1
ORDER BY curr.Name DESC, curr.[Modified Date] DESC

输出结果 #

和预期完全匹配：

注意事项 #

• 字段名Modified Date包含空格，查询时必须用方括号包裹避免语法报错
• 如果需要调整排序规则，修改ORDER BY后的参数即可

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