27,579
社区成员
发帖
与我相关
我的任务
分享
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,
占用 = 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