SQL Server 2008-R2查询：获取记录前值并与当前值同展示

我有一张记录房间（room_id）状态（condition_id）变更情况及变更时间（date_registered）的表，使用SQL Server 2008-R2。需要编写查询语句，在指定日期范围内，展示每个发生状态变更的房间的原状态与当前状态。

示例测试表及数据：

DECLARE @tbl TABLE (
    room_id         numeric(10,0)   null,
    condition_id    numeric(10,0)   null,
    date_registered datetime        null
);

INSERT @tbl (room_id, condition_id, date_registered)
VALUES
(1,2,'2018-12-07 08:37:19.300'),
(2,1,'2018-12-08 08:37:19.300'),
(1,3,'2018-12-09 08:37:19.300'),
(2,2,'2018-12-10 08:37:19.300'),
(1,1,'2018-12-11 08:37:19.300');

期望查询结果：

room_id old_condition_id    condition_id    date_registered
1       2                   3               2018-12-09 08:37:19.300
2       1                   2               2018-12-10 08:37:19.300
1       3                   1               2018-12-11 08:37:19.300   

由于SQL Server 2008-R2不支持LAG()这类窗口函数，我们可以通过ROW_NUMBER()为每个房间的状态记录按时间排序，再通过自连接关联前一条记录来获取原状态。

查询语句如下：

-- 定义日期范围参数
DECLARE @StartDate DATETIME = '2018-12-01',
        @EndDate DATETIME = '2018-12-31';

WITH RoomStatus AS (
    SELECT 
        room_id,
        condition_id,
        date_registered,
        -- 按房间分组，按变更时间升序编号
        ROW_NUMBER() OVER (PARTITION BY room_id ORDER BY date_registered) AS rn
    FROM @tbl
    WHERE date_registered BETWEEN @StartDate AND @EndDate
)
SELECT 
    curr.room_id,
    prev.condition_id AS old_condition_id,
    curr.condition_id,
    curr.date_registered
FROM RoomStatus curr
-- 关联当前记录的前一条记录（rn-1）
JOIN RoomStatus prev ON curr.room_id = prev.room_id AND curr.rn = prev.rn + 1
ORDER BY curr.date_registered;

说明： #

• 先用CTERoomStatus为每个房间的状态记录按时间排序并编号，rn越小代表记录时间越早；
• 通过自连接将当前记录（curr）与同房间的上一条记录（prev）关联，prev的condition_id就是原状态；
• 可以通过@StartDate和@EndDate参数筛选指定日期范围的记录；
• 最终结果按变更时间排序，与示例输出一致。

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