如何查询SQL Server系统版本表指定列的变更历史？

问题描述 #

我有一个包含约20列的SQL Server系统版本表，所有列的值都会随时间发生变化。希望获取仅部分列（如Name、Company）的变更值及其对应的有效期列（SysStartTime、SysEndTime），忽略其他列（如Location）的变更，但保留因这些变更生成的SysStartTime/SysEndTime值。后续可能还需要扩展到更多列，忽略其他非目标列的变更。

示例数据 #

期望输出 #

解决方案 #

核心思路是将连续的、目标列值相同的行归为一组，每组取最早的SysStartTime和最晚的SysEndTime，以此忽略非目标列的变更并保留正确时间区间。使用窗口函数实现分组标记，再聚合得到结果：

WITH RankedRows AS (
    SELECT 
        Name,
        Company,
        SysStartTime,
        SysEndTime,
        -- 标记当前行与上一行目标列是否一致，不一致则生成新组
        SUM(CASE WHEN 
            LAG(Name) OVER (PARTITION BY Name ORDER BY SysStartTime) = Name 
            AND LAG(Company) OVER (PARTITION BY Name ORDER BY SysStartTime) = Company 
            THEN 0 ELSE 1 END) 
        OVER (PARTITION BY Name ORDER BY SysStartTime) AS GroupId
    FROM YourTableName -- 替换为你的实际表名
)
SELECT 
    Name,
    Company,
    MIN(SysStartTime) AS SysStartTime,
    MAX(SysEndTime) AS SysEndTime
FROM RankedRows
GROUP BY Name, Company, GroupId
ORDER BY Name, SysStartTime;

关键说明 #

1. PARTITION BY Name：按员工姓名单独分组处理，避免不同员工的记录相互干扰
2. LAG()函数：获取当前行的上一行记录，对比目标列（Name、Company）是否一致
3. SUM() OVER()：生成分组ID，每当目标列发生变化时分组ID递增，将连续相同的目标列记录归为同一组
4. GROUP BY聚合：按分组ID合并记录，取每组的最小起始时间和最大结束时间，得到合并后的有效区间

扩展目标列的方法 #

如果后续需要新增目标列（如Department），只需在CASE WHEN条件中添加对应列的对比即可：

SUM(CASE WHEN 
    LAG(Name) OVER (PARTITION BY Name ORDER BY SysStartTime) = Name 
    AND LAG(Company) OVER (PARTITION BY Name ORDER BY SysStartTime) = Company
    AND LAG(Department) OVER (PARTITION BY Name ORDER BY SysStartTime) = Department -- 新增列对比
    THEN 0 ELSE 1 END) 
OVER (PARTITION BY Name ORDER BY SysStartTime) AS GroupId

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