SQL实现：未发货批次无间隙列总和与分组统计

表结构与需求回顾 #

表结构 #

• Table1（批次表）：存储批次信息，字段包括 ID、Quantity（单箱物品数量）、FK_FirstItem（关联物品表起始物品ID）、FK_LastItem（关联物品表结束物品ID）、FK_ShippentID（发货标识，NULL代表未发货）
• Table2（物品表）：存储物品信息，Serialnum 为连续序列，字段包括 ItemID、Serialnum、Barcode

核心需求 #

筛选出FK_ShippentID IS NULL的未发货批次，按Serialnum无间隙连续段分组，每组需输出：

• 该段总物品数量（所有批次Quantity之和）
• 该段包含的箱数（批次记录数）
• 段起始Serialnum
• 段结束Serialnum

解决方案 #

方法一：适用于批次区间连续无重叠场景 #

利用窗口函数生成分组标识，将连续的批次归为同一组：

WITH batch_serial AS (
    -- 关联批次表与物品表，获取每个未发货批次的起止Serialnum
    SELECT
        t1.ID,
        t1.Quantity,
        t_start.Serialnum AS s_start,
        t_end.Serialnum AS s_end
    FROM Table1 t1
    JOIN Table2 t_start ON t1.FK_FirstItem = t_start.ItemID
    JOIN Table2 t_end ON t1.FK_LastItem = t_end.ItemID
    WHERE t1.FK_ShippentID IS NULL
),
grouped_batches AS (
    -- 生成分组ID：判断当前批次与上一批次是否连续，不连续则新建分组
    SELECT
        *,
        SUM(CASE WHEN s_start = LAG(s_end) OVER (ORDER BY s_start) + 1 THEN 0 ELSE 1 END) 
        OVER (ORDER BY s_start) AS group_id
    FROM batch_serial
)
-- 按分组统计结果
SELECT
    MIN(s_start) AS segment_start_serial,
    MAX(s_end) AS segment_end_serial,
    SUM(Quantity) AS total_quantity,
    COUNT(*) AS box_count
FROM grouped_batches
GROUP BY group_id
ORDER BY segment_start_serial;

逻辑说明

1. batch_serial CTE：关联两张表，提取每个未发货批次对应的Serialnum起止区间。
2. grouped_batches CTE：通过LAG()窗口函数获取上一批次的结束Serialnum，若当前批次的起始Serialnum不等于上一批次结束值+1，则标记为新分组，最终所有连续批次会被分到同一个group_id下。
3. 最后按group_id聚合，得到每个连续段的统计数据。

方法二：适用于批次区间存在重叠/嵌套场景 #

如果业务中存在批次区间重叠（如A批次覆盖1-15，B批次覆盖10-20），需用递归CTE合并区间：

WITH batch_serial AS (
    SELECT
        t1.ID,
        t1.Quantity,
        t_start.Serialnum AS s_start,
        t_end.Serialnum AS s_end
    FROM Table1 t1
    JOIN Table2 t_start ON t1.FK_FirstItem = t_start.ItemID
    JOIN Table2 t_end ON t1.FK_LastItem = t_end.ItemID
    WHERE t1.FK_ShippentID IS NULL
),
merged_segments AS (
    -- 初始数据：按起始Serialnum排序
    SELECT
        s_start,
        s_end,
        Quantity,
        CAST(ID AS VARCHAR) AS batch_ids
    FROM batch_serial
    ORDER BY s_start
    UNION ALL
    -- 递归合并重叠/连续的区间
    SELECT
        ms.s_start,
        GREATEST(ms.s_end, bs.s_end),
        ms.Quantity + bs.Quantity,
        CONCAT(ms.batch_ids, ',', bs.ID)
    FROM merged_segments ms
    JOIN batch_serial bs 
        ON ms.s_end >= bs.s_start - 1  -- 连续或重叠的判定条件
        AND bs.s_start > ms.s_start
    WHERE NOT EXISTS (
        -- 排除已被更大区间包含的记录
        SELECT 1 FROM merged_segments ms2 
        WHERE ms2.s_start <= ms.s_start AND ms2.s_end >= ms.s_end AND ms2.batch_ids <> ms.batch_ids
    )
),
unique_segments AS (
    -- 去重，保留每个合并区间的最大范围及统计值
    SELECT
        s_start,
        MAX(s_end) AS s_end,
        SUM(Quantity) AS total_quantity,
        COUNT(DISTINCT ID) AS box_count
    FROM merged_segments
    CROSS APPLY STRING_SPLIT(batch_ids, ',') AS split_ids
    GROUP BY s_start
)
-- 最终统计合并后的连续段
SELECT
    MIN(s_start) AS segment_start_serial,
    MAX(s_end) AS segment_end_serial,
    SUM(total_quantity) AS total_quantity,
    SUM(box_count) AS box_count
FROM unique_segments
GROUP BY segment_start_serial
ORDER BY segment_start_serial;

逻辑说明

1. 递归CTEmerged_segments会不断合并重叠或连续的区间，直到没有可合并的区间为止。
2. unique_segments通过拆分批次ID字符串，统计每个合并区间的总物品数和箱数。
3. 最后聚合得到最终的连续段统计结果。

使用说明 #

• 上述查询可直接作为动态查询复用，只需确保表名和字段名与实际业务一致。
• 根据批次区间的实际情况选择对应方法：无重叠场景用方法一，性能更优；有重叠场景用方法二，兼容性更强。

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