在论坛上看到这样一个需求,有两个表,数据分别如下
表A (id, name, value )
(1, ‘A’, 2000),
(2, ‘A’, 2000)
表B (id, a_name, name, value)
(1, ‘A’, ‘A1’, 1000),
(2, ‘A’, ‘A2’, 2000),
(3, ‘A’, ‘A3’, 3000)
要求根据表A的 value 值,查询出对表B对应(B.a_name = A.name) value 的占用,也就是如下结果:
id a_name name value A.id A.value 占用
----------- ------ ---- ----------- ----------- ----------- -----------
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
需求非常简单明了,对于A、B排序后,根据顺序对应做占有分配即可,但查询写起来有点绕(以前做过类似的,但好久没写了,尝试写了一下之后发现有些头晕,所以在这里记录一下),最终实现的查询脚本如下:
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
思路提示:
两个表的记录的范围对应,是根据数据区间的交叉性确定
占用值的确定,是数据区间交叉部分的位置确定
(画图理解会更直观)