22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT a.用户ID,a.数值,b.数据序列,a.数值 AS 数据值
FROM 用户ID表 a, 分配表 b
WITH a(Code,ReqNum) AS (
SELECT 'A', 500 UNION ALL
SELECT 'B',1000 UNION ALL
SELECT 'C',1500 UNION ALL
SELECT 'D', 500 UNION ALL
SELECT 'E',1000 UNION ALL
SELECT 'F', 500
)
,b(SN,Value) AS (
SELECT 'm7',1200 UNION ALL
SELECT 'm8',1800 UNION ALL
SELECT 'm9',2000
)
,a1 AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY Code) rn
FROM a
)
,b1 AS (
SELECT *,
ROW_NUMBER() OVER(ORDER BY sn) rn
FROM b
)
,r AS (
-- 第一条
SELECT a1.Code,
a1.ReqNum,
b1.SN,
CASE WHEN a1.ReqNum <= b1.value THEN
a1.ReqNum
ELSE
b1.value
END AS AssignNum,
b1.value - a1.ReqNum AS RemainNum,
a1.rn AS a_rn,
b1.rn AS b_rn
FROM a1,b1
WHERE a1.rn = 1
AND b1.rn = 1
UNION ALL -- 分配有剩余,继续下一个用户
SELECT a1.Code,
a1.ReqNum,
r.SN,
CASE WHEN a1.ReqNum <= r.RemainNum THEN
a1.ReqNum
ELSE
r.RemainNum
END AS AssignNum,
r.RemainNum - a1.ReqNum AS RemainNum,
a1.rn AS a_rn,
r.b_rn
FROM r
JOIN a1
ON a1.rn = r.a_rn + 1
WHERE r.RemainNum > 0
UNION ALL -- 分配不足,继续下一个分配序列
SELECT r.Code,
r.ReqNum,
b1.SN,
CASE WHEN ABS(r.RemainNum) <= b1.value THEN
ABS(r.RemainNum)
ELSE
b1.value
END AS AssignNum,
b1.value - ABS(r.RemainNum) AS RemainNum,
r.a_rn,
b1.rn AS b_rn
FROM r
JOIN b1
ON b1.rn = r.b_rn + 1
WHERE r.RemainNum < 0
UNION ALL -- 正好分配完,两边都继续下一个
SELECT a1.Code,
a1.ReqNum,
b1.SN,
CASE WHEN a1.ReqNum <= b1.value THEN
a1.ReqNum
ELSE
b1.value
END AS AssignNum,
b1.value - a1.ReqNum AS RemainNum,
a1.rn AS a_rn,
b1.rn AS b_rn
FROM r
JOIN a1
ON a1.rn = r.a_rn + 1
JOIN b1
ON b1.rn = r.b_rn + 1
WHERE r.RemainNum = 0
)
SELECT *
FROM r
--OPTION (MAXRECURSION 1000)
Code ReqNum SN AssignNum RemainNum a_rn b_rn
---- ----------- ---- ----------- ----------- -------------------- --------------------
A 500 m7 500 700 1 1
B 1000 m7 700 -300 2 1
B 1000 m8 300 1500 2 2
C 1500 m8 1500 0 3 2
D 500 m9 500 1500 4 3
E 1000 m9 1000 500 5 3
F 500 m9 500 0 6 3