请求编写SQL/PLSQL查询实现数值按指定区间拆分

给定数据 #

T1表（原始数值表） #

T2表（区间配置表） #

需求说明 #

将每个Name对应的No数值，按T2中该Name的区间顺序依次扣除区间最大可分配值（即End Range - Start Range）：

• 若当前剩余数值大于区间容量，取区间容量作为该区间的拆分值，剩余数值减去区间容量；
• 若当前剩余数值小于等于区间容量，取剩余数值作为该区间的拆分值，剩余数值置为0；
• 若区间容量为0（如BBB的第三个区间），拆分值直接为0；
• 所有区间处理完成后，若仍有剩余数值，需额外输出该剩余值。

预期输出 #

SQL查询语句 #

以下SQL使用窗口函数计算累计区间容量，适用于支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、Oracle等）：

WITH t2_with_capacity AS (
    SELECT 
        name,
        start_range,
        end_range,
        end_range - start_range AS interval_capacity,
        SUM(end_range - start_range) OVER (PARTITION BY name ORDER BY start_range) AS cumulative_capacity
    FROM t2
),
t1_with_remaining AS (
    SELECT 
        name,
        no AS original_no,
        no AS remaining_no
    FROM t1
)
SELECT 
    t2c.name,
    CASE
        WHEN t2c.interval_capacity = 0 THEN 0
        WHEN t2c.cumulative_capacity <= t1wr.original_no THEN t2c.interval_capacity
        WHEN t2c.cumulative_capacity - t2c.interval_capacity < t1wr.original_no THEN 
            t1wr.original_no - (t2c.cumulative_capacity - t2c.interval_capacity)
        ELSE 0
    END AS no
FROM t2_with_capacity t2c
JOIN t1_with_remaining t1wr ON t2c.name = t1wr.name
UNION ALL
SELECT 
    name,
    original_no - cumulative_capacity AS no
FROM (
    SELECT 
        t1wr.name,
        t1wr.original_no,
        MAX(t2c.cumulative_capacity) AS cumulative_capacity
    FROM t1_with_remaining t1wr
    JOIN t2_with_capacity t2c ON t1wr.name = t2c.name
    GROUP BY t1wr.name, t1wr.original_no
) sub
WHERE original_no > cumulative_capacity
ORDER BY name, 
         CASE WHEN no = original_no - cumulative_capacity THEN 1 ELSE 0 END,
         start_range;

语句说明 #

1. t2_with_capacity：计算每个区间的容量，并按Name分组、区间起始排序计算累计容量；
2. t1_with_remaining：保留原始数值作为剩余值的初始值；
3. 主查询：根据累计容量和原始数值的关系，计算每个区间的拆分值；
4. UNION ALL部分：处理所有区间累计容量小于原始值的情况，输出剩余数值；
5. 最终排序保证每个Name的区间按顺序排列，剩余值在最后。

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