求SQL实现按输入范围拆分数值区间并匹配请求容量的查询语句

搞定这个拆分需求的核心是理清优先级：先从指定的数值范围里提取容量，若还没满足请求的Volume=3，再从原数据的其他区间补充，最后把被取用的部分标记为Yes，剩余部分标记为No。以下是实现这个逻辑的SQL查询（基于PostgreSQL语法，其他数据库可稍作调整）：

WITH request_params AS (
    -- 把请求参数定义成CTE，方便后续复用：总需求容量和指定的数值范围
    SELECT 3 AS required_volume,
           unnest(ARRAY[ROW(301, 301), ROW(103, 103)]) AS range(start_num, end_num)
),
record_intersections AS (
    -- 第一步：找出原表记录和请求范围的交集，计算每个交集能提供的容量
    SELECT 
        t.SlNo,
        t.NumberStart AS original_start,
        t.NumberEnd AS original_end,
        t.Volume AS original_volume,
        GREATEST(t.NumberStart, r.start_num) AS intersect_start,
        LEAST(t.NumberEnd, r.end_num) AS intersect_end,
        -- 每个交集对应的容量（假设每个数值对应1单位容量）
        LEAST(t.NumberEnd, r.end_num) - GREATEST(t.NumberStart, r.start_num) + 1 AS intersect_volume,
        rp.required_volume
    FROM table1 t
    CROSS JOIN request_params rp
    CROSS JOIN unnest(rp.range) r
    -- 只保留确实有交集的记录
    WHERE GREATEST(t.NumberStart, r.start_num) <= LEAST(t.NumberEnd, r.end_num)
),
allocated_volumes AS (
    -- 第二步：计算每个原记录需要分配的总容量，优先满足请求范围的需求
    SELECT 
        SlNo,
        original_start,
        original_end,
        original_volume,
        SUM(intersect_volume) AS requested_intersect_volume,
        required_volume,
        -- 取交集总容量、剩余需求、原记录容量三者的最小值，确定实际分配量
        LEAST(SUM(intersect_volume), required_volume, original_volume) AS allocated_total
    FROM record_intersections
    GROUP BY SlNo, original_start, original_end, original_volume, required_volume
    UNION ALL
    -- 处理请求范围没覆盖到，但需要补充容量的记录（如果需求还没满足）
    SELECT 
        t.SlNo,
        t.NumberStart AS original_start,
        t.NumberEnd AS original_end,
        t.Volume AS original_volume,
        0 AS requested_intersect_volume,
        -- 计算还缺多少容量
        rp.required_volume - COALESCE((SELECT SUM(allocated_total) FROM allocated_volumes), 0) AS remaining_required,
        LEAST(t.Volume, rp.required_volume - COALESCE((SELECT SUM(allocated_total) FROM allocated_volumes), 0)) AS allocated_total
    FROM table1 t
    CROSS JOIN request_params rp
    WHERE (SELECT SUM(allocated_total) FROM allocated_volumes) < rp.required_volume
    AND NOT EXISTS (SELECT 1 FROM record_intersections ri WHERE ri.SlNo = t.SlNo)
),
split_segments AS (
    -- 第三步：生成所有拆分后的片段
    -- 1. 请求范围覆盖的拆分片段
    SELECT 
        ri.intersect_start AS NumberStart,
        ri.intersect_end AS NumberEnd,
        'Yes' AS isSplitted,
        -- 确保分配的容量不超过该交集的可用量和剩余需求
        LEAST(ri.intersect_volume, av.allocated_total - COALESCE(SUM(ri2.intersect_volume) OVER (PARTITION BY ri.SlNo ORDER BY ri.intersect_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)) AS Volume
    FROM record_intersections ri
    JOIN allocated_volumes av ON ri.SlNo = av.SlNo
    WHERE av.allocated_total > 0
    UNION ALL
    -- 2. 补充容量的拆分片段（从非请求范围取的部分）
    SELECT 
        av.original_start AS NumberStart,
        av.original_start + av.allocated_total - 1 AS NumberEnd,
        'Yes' AS isSplitted,
        av.allocated_total AS Volume
    FROM allocated_volumes av
    WHERE av.requested_intersect_volume = 0 AND av.allocated_total > 0
    UNION ALL
    -- 3. 原记录剩余的未拆分片段
    SELECT 
        -- 计算剩余片段的起始位置
        CASE 
            WHEN EXISTS (SELECT 1 FROM record_intersections ri WHERE ri.SlNo = av.SlNo) THEN 
                GREATEST(av.original_start, (SELECT MAX(ri.intersect_end) FROM record_intersections ri WHERE ri.SlNo = av.SlNo) + 1)
            ELSE av.original_start + av.allocated_total
        END AS NumberStart,
        av.original_end AS NumberEnd,
        'No' AS isSplitted,
        av.original_volume - av.allocated_total AS Volume
    FROM allocated_volumes av
    WHERE av.original_volume - av.allocated_total > 0
)
-- 最终结果按NumberStart排序，和示例输出一致
SELECT NumberStart, NumberEnd, isSplitted, Volume
FROM split_segments
ORDER BY NumberStart;

关键逻辑说明 #

• request_params：把请求的总容量和指定范围封装成可复用的CTE，后续修改参数只需要改这里。
• record_intersections：精准定位原表和请求范围的重叠部分，计算每个重叠区间能提供的容量。
• allocated_volumes：确定每个原记录需要贡献多少容量，优先用请求范围里的，不够再从其他地方补。
• split_segments：把原记录拆成三种类型的片段：被请求用掉的（Yes）、补充用掉的（Yes）、剩下的（No）。

如果用的是MySQL、SQL Server这类数据库，需要调整unnest、窗口函数的语法（比如MySQL用JSON_TABLE处理数组，SQL Server用表值构造函数定义请求范围），核心逻辑是通用的。

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