Oracle SQL中基于两张表计算仓库物品可用编号范围

要生成每个Item_ID的可用编号范围，核心是找出入库区间（表A）中未被出库区间（表B）覆盖的连续段。以下是具体实现：

实现思路 #

1. 关联同Item_ID下与入库区间有重叠的所有出库区间，按起始编号排序
2. 拆分入库区间为三类未被覆盖的有效分段：
   • 入库起始到第一个出库起始的前一位（若该区间有效）
   • 相邻出库区间之间的间隙（若该区间有效）
   • 最后一个出库结束的后一位到入库结束（若该区间有效）
3. 直接保留完全未被出库覆盖的入库区间

视图创建SQL #

CREATE OR REPLACE VIEW AVAILABLE_ITEM_RANGES AS
WITH sorted_out AS (
    SELECT 
        b.Item_ID,
        b.Start_NUM,
        b.End_NUM,
        a.id AS a_id,
        a.Start_NUM AS a_start,
        a.End_NUM AS a_end,
        ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.Start_NUM) AS rn
    FROM A
    LEFT JOIN B ON a.Item_ID = b.Item_ID 
        AND b.Start_NUM <= a.End_NUM 
        AND b.End_NUM >= a.Start_NUM
),
split_ranges AS (
    -- 处理入库区间开头未被覆盖的部分
    SELECT 
        a_id,
        Item_ID,
        a_start AS avail_start_num,
        CASE WHEN Start_NUM IS NOT NULL THEN Start_NUM - 1 ELSE a_end END AS avail_end_num
    FROM sorted_out
    WHERE rn = 1
    UNION ALL
    -- 处理出库区间之间的间隙
    SELECT 
        curr.a_id,
        curr.Item_ID,
        curr.End_NUM + 1 AS avail_start_num,
        next.Start_NUM - 1 AS avail_end_num
    FROM sorted_out curr
    JOIN sorted_out next ON curr.a_id = next.a_id AND curr.rn = next.rn - 1
    UNION ALL
    -- 处理入库区间结尾未被覆盖的部分
    SELECT 
        a_id,
        Item_ID,
        End_NUM + 1 AS avail_start_num,
        a_end AS avail_end_num
    FROM sorted_out
    WHERE rn = (SELECT MAX(rn) FROM sorted_out WHERE a_id = sorted_out.a_id)
        AND End_NUM < a_end
)
-- 过滤无效区间，生成最终结果并添加自增ID
SELECT 
    ROW_NUMBER() OVER (ORDER BY Item_ID, avail_start_num) AS ID,
    Item_ID AS ITEMID,
    TO_CHAR(avail_start_num, 'FM999G999G999') AS AVAIL_START_NUM,
    TO_CHAR(avail_end_num, 'FM999G999G999') AS AVAIL_END_NUM
FROM split_ranges
WHERE avail_start_num <= avail_end_num
ORDER BY Item_ID, avail_start_num;

结果验证 #

查询该视图后，返回结果与预期一致：

代码说明 #

• sorted_out CTE：关联入库与重叠的出库记录，按出库起始编号排序，标记每条出库记录在对应入库区间内的顺序
• split_ranges CTE：拆分入库区间为三类有效未覆盖段
• 最终查询：过滤无效区间（起始编号大于结束编号），格式化编号为带千分位的字符串，生成自增ID

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