See such a requirement in the forum, there are two tables, the data are as follows
Table a (ID, name, value)
(1, 'A', 2000),
(2, 'A', 2000)
Table B (ID, a_name, name, value)
(1, 'A', 'A1', 1000),
(2, 'A', 'A2', 2000),
(3, 'A', 'A3', 3000)
According to the value value of table A, it is required to query the occupation of the value corresponding to table B (B.a_name = A.name), which is the following result:

id          a_name name value       A.id          A.value       Occupy
----------- ------ ---- ----------- ----------- ----------- -----------
1           A      A1   1000        1           2000        1000
2           A      A2   2000        1           2000        1000
2           A      A2   2000        6           2000        1000
3           A      A3   3000        6           2000        1000

The requirements are very simple and clear. After sorting A and B, it's enough to allocate the ownership according to the corresponding order, but the query is A bit convoluted (I've done similar things before, but haven't written them for A long time, and I found that I was dizzy after trying to write them, so I'll record them here). The final query script is as follows:

WITH
tb1 AS(
    SELECT * FROM(VALUES
        (1, 'A', 2000),
        --(2, 'A', 200),
        --(3, 'A', 200),
        --(4, 'A', 1000),
        --(5, 'A', 2000),
        (6, 'A', 2000)
    ) D(id, name, value )
),
tb2 AS(
    SELECT * FROM(VALUES
        (1, 'A', 'A1', 1000),
        (2, 'A', 'A2', 2000),
        (3, 'A', 'A3', 3000)
    ) D(id, a_name, name, value )
),
A AS(
    SELECT *, s_value1 = s_value2-value FROM(
        SELECT *, s_value2 = SUM(value)OVER(PARTITION BY name ORDER BY id)
        FROM tb1
    )D
),
B AS(
    SELECT *, s_value1 = s_value2-value FROM(
        SELECT *, s_value2 = SUM(value)OVER(PARTITION BY a_name ORDER BY id)
        FROM tb2
    )D
)
SELECT B.id, B.a_name, B.name, B.value,
    A.id, A.value,
    //Occupation = CASE
        WHEN A.s_value1 >= B.s_value1 AND A.s_value2 <= B.s_value2 THEN A.value
        WHEN B.s_value1 >= A.s_value1 AND B.s_value2 <= A.s_value2 THEN B.value
        WHEN A.s_value2 > B.s_value2 THEN A.value - (A.s_value2-B.s_value2)
        ELSE A.s_value2 - B.s_value1
    END
FROM B, A
WHERE B.a_name = A.name
    AND B.s_value1 < A.s_value2 AND B.s_value2 >= A.s_value1

Tips:
The range correspondence between the records of the two tables is determined according to the intersection of data intervals
The determination of occupancy value is the determination of the position of the intersection part of data interval
(drawing comprehension will be more intuitive)