如何将指定数值按给定区间拆分？求SQL/PLSQL查询语句

给定数值为2111.03，关联两张表的数据如下：

T1表数据 #

T2表数据（区间范围） #

需求说明 #

将T1表的数值按T2表的区间顺序，依次扣除对应区间的最大值（即区间上限与下限的差值），最终输出每个区间扣除的数值，以及最后剩余的数值。

预期输出 #

WITH t1_data AS (
    SELECT 'AAA' AS Name, 2111.03 AS No FROM DUAL
),
t2_data AS (
    SELECT 'AAA' AS Name, 0 AS Start_Range, 100 AS End_Range FROM DUAL UNION ALL
    SELECT 'AAA' AS Name, 100 AS Start_Range, 130 AS End_Range FROM DUAL UNION ALL
    SELECT 'AAA' AS Name, 130 AS Start_Range, 155 AS End_Range FROM DUAL UNION ALL
    SELECT 'AAA' AS Name, 155 AS Start_Range, 99990 AS End_Range FROM DUAL
),
t2_with_cumulative AS (
    SELECT 
        Name,
        Start_Range,
        End_Range,
        End_Range - Start_Range AS interval_max,
        SUM(End_Range - Start_Range) OVER (PARTITION BY Name ORDER BY Start_Range) AS cumulative_total
    FROM t2_data
)
SELECT 
    t2.Name,
    CASE 
        WHEN t1.No >= t2.cumulative_total THEN t2.interval_max
        WHEN t1.No >= (t2.cumulative_total - t2.interval_max) THEN t1.No - (t2.cumulative_total - t2.interval_max)
        ELSE 0
    END AS No
FROM t2_with_cumulative t2
JOIN t1_data t1 ON t2.Name = t1.Name
ORDER BY t2.Start_Range;

逻辑说明 #

1. 先计算每个区间的最大值（interval_max = End_Range - Start_Range）
2. 通过窗口函数计算区间最大值的累计总和，用于判断当前数值在哪个区间范围内
3. 用CASE分支判断：如果累计总和小于等于原数值，则扣除整个区间最大值；如果原数值落在当前区间内，则扣除剩余部分；否则扣除0

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