如何扩展SQL查询获取资产当前活跃预订及后续预订并标记状态？

表结构与数据 #

--------
Bookings
--------

BookingID      AssetNo   Start                      End                       Status
---------      -------   -----------------------    -----------------------   ------
1              1         2023-05-31 00:00:00.000    2023-05-31 01:00:00.000   Overdue
2              1         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   Booked
3              2         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   InUse
4              2         2023-05-31 02:00:00.000    2023-05-31 03:00:00.000   Booked
5              2         2023-05-31 03:00:00.000    2023-05-31 04:00:00.000   Booked

需求说明 #

针对每个资产，查询当前时间下的活跃预订（包含两种情况：当前时间处于预订时间段内的InUse状态，或已过结束时间但状态为Overdue的逾期预订），以及该资产在活跃预订之后的下一个预订（若存在），同时标记每条记录是否为活跃预订（Active = True表示活跃，False表示后续预订）。

以当前时间2023-05-31 01:30:00.000为例，期望返回结果：

BookingID      AssetNo   Start                      End                       Status   Active
---------      -------   -----------------------    -----------------------   ------   ------
1              1         2023-05-31 00:00:00.000    2023-05-31 01:00:00.000   Overdue  True
2              1         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   Booked   False
3              2         2023-05-31 01:00:00.000    2023-05-31 02:00:00.000   InUse    True
4              2         2023-05-31 02:00:00.000    2023-05-31 03:00:00.000   Booked   False

已实现的活跃预订查询 #

DECLARE @UTCNow DATETIME;
SET @UTCNow = '2023-05-31 01:30:00.000';

SELECT * FROM (
    SELECT
        b.*
        ,RANK() OVER (PARTITION BY b.AssetNo ORDER BY [Start] ASC) AS time_order
    FROM Bookings b
    WHERE (b.[End] < @UTCNow AND b.Status = 'Overdue') -- 逾期未归还的历史预订
    OR (b.[Start] <= @UTCNow AND b.[End] > @UTCNow) -- 当前正在进行的预订
    ) AS b2
WHERE b2.time_order = 1

问题 #

如何扩展上述查询，同时获取活跃预订之后的下一个预订，并标记Active字段？优先采用ANSI SQL标准，也可使用Transact-SQL。

用户尝试思路：先获取每个资产当前活跃预订的开始时间，再按Start升序选择该时间之后的前2条预订。

解决方案 #

方案1：ANSI SQL兼容版本 #

WITH AllBookings AS (
    -- 为每个资产的预订按开始时间排序，分配唯一序号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY AssetNo ORDER BY Start ASC) AS rn
    FROM Bookings
),
ActiveBookings AS (
    -- 筛选每个资产的活跃预订，并记录其序号
    SELECT 
        ab.AssetNo,
        ab.rn AS active_rn
    FROM AllBookings ab
    CROSS JOIN (SELECT '2023-05-31 01:30:00.000' AS current_time) AS ct
    WHERE (ab.End < ct.current_time AND ab.Status = 'Overdue')
       OR (ab.Start <= ct.current_time AND ab.End > ct.current_time)
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ab.AssetNo ORDER BY ab.Start ASC) = 1
)
-- 选取活跃预订及下一个预订，并标记状态
SELECT 
    abk.BookingID,
    abk.AssetNo,
    abk.Start,
    abk.End,
    abk.Status,
    CASE WHEN abk.rn = ab.active_rn THEN 'True' ELSE 'False' END AS Active
FROM AllBookings abk
JOIN ActiveBookings ab ON abk.AssetNo = ab.AssetNo
WHERE abk.rn IN (ab.active_rn, ab.active_rn + 1)
ORDER BY abk.AssetNo, abk.rn;

方案2：Transact-SQL版本（适配SQL Server 2022及更早版本） #

DECLARE @UTCNow DATETIME;
SET @UTCNow = '2023-05-31 01:30:00.000';

WITH AllBookings AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY AssetNo ORDER BY Start ASC) AS rn
    FROM Bookings
),
ActiveBookings AS (
    SELECT * FROM (
        SELECT 
            ab.AssetNo,
            ab.rn AS active_rn,
            ROW_NUMBER() OVER (PARTITION BY ab.AssetNo ORDER BY ab.Start ASC) AS rank
        FROM AllBookings ab
        WHERE (ab.End < @UTCNow AND ab.Status = 'Overdue')
           OR (ab.Start <= @UTCNow AND ab.End > @UTCNow)
    ) t
    WHERE t.rank = 1
)
SELECT 
    abk.BookingID,
    abk.AssetNo,
    abk.Start,
    abk.End,
    abk.Status,
    CASE WHEN abk.rn = ab.active_rn THEN 'True' ELSE 'False' END AS Active
FROM AllBookings abk
JOIN ActiveBookings ab ON abk.AssetNo = ab.AssetNo
WHERE abk.rn IN (ab.active_rn, ab.active_rn + 1)
ORDER BY abk.AssetNo, abk.rn;

逻辑说明 #

1. AllBookings：为每个资产的所有预订按Start时间升序分配唯一序号rn，用于定位预订顺序；
2. ActiveBookings：筛选出每个资产的活跃预订，并记录其对应的序号active_rn；
3. 最后通过关联两个CTE，筛选出序号等于active_rn（活跃预订）和active_rn+1（下一个预订）的记录，用CASE表达式标记Active状态。

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