如何在SQL Server中识别货箱的空箱时间区间？

别用游标，更没必要嵌套游标+局部变量跟踪库存——基于集合的SQL查询才是最优方案，性能和可维护性都甩游标几条街。

核心思路 #

1. 先把export表的异动数据按货箱ID、操作时间排序（你的示例数据里时间有乱序，必须先排好）
2. 结合初始库存，计算每个时间点的实时库存（初始库存 + 累计异动数量）
3. 识别连续的空箱状态区间：当实时库存变为0时标记区间开始，直到库存回到正数时标记区间结束

具体实现代码 #

假设你有存储过程GetLatestBoxInventory返回每个货箱的最新初始库存（比如示例里的35），我们可以把它和export表关联，然后用窗口函数计算累计库存：

WITH BoxInventory AS (
    -- 获取每个货箱的初始库存
    SELECT idbox, initial_quantity FROM dbo.GetLatestBoxInventory()
),
SortedActions AS (
    -- 按货箱和时间排序异动记录
    SELECT 
        e.idbox,
        e.actualquantity,
        e.actiondate,
        -- 计算累计异动数量（按时间顺序）
        SUM(e.actualquantity) OVER (PARTITION BY e.idbox ORDER BY e.actiondate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_qty
    FROM dbo.export e
),
RealTimeInventory AS (
    -- 计算每个时间点的实时库存
    SELECT
        sa.idbox,
        sa.actiondate,
        bi.initial_quantity + sa.cumulative_qty AS current_qty,
        -- 判断当前是否为空箱
        CASE WHEN bi.initial_quantity + sa.cumulative_qty = 0 THEN 1 ELSE 0 END AS is_empty,
        -- 标记空箱状态的分组（用于识别连续区间）
        SUM(CASE WHEN bi.initial_quantity + sa.cumulative_qty = 0 THEN 0 ELSE 1 END) OVER (PARTITION BY sa.idbox ORDER BY sa.actiondate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS empty_group
    FROM SortedActions sa
    JOIN BoxInventory bi ON sa.idbox = bi.idbox
)
-- 提取空箱区间
SELECT
    idbox,
    MIN(actiondate) AS empty_start_time,
    MAX(actiondate) AS empty_end_time
FROM RealTimeInventory
WHERE is_empty = 1
GROUP BY idbox, empty_group
ORDER BY idbox, empty_start_time;

代码解释 #

1. BoxInventory：获取每个货箱的初始库存，替换成你的存储过程调用即可
2. SortedActions：对异动记录排序，并用SUM() OVER()窗口函数计算累计异动数量，确保按时间顺序累加
3. RealTimeInventory：计算实时库存，然后用SUM() OVER()生成分组标识——当库存从非空变空时，分组ID不变；一旦回到非空，分组ID递增，这样就能把连续的空箱记录归为同一组
4. 最后按分组聚合，取每组的最小/最大时间，就是空箱的起止区间

为什么不用游标？ #

• 游标是逐行处理，数据量大时性能极差，尤其是嵌套游标
• 基于集合的查询是SQL Server的原生优化方向，引擎能更好地执行计划优化
• 代码更简洁，逻辑清晰，后期维护成本低

针对你的示例数据 #

用初始库存35代入，运行后会得到空箱区间：2022-08-15 00:00:00.0000000 到 2022-08-24 00:00:00.0000000，和你预期的结果一致。

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