SQLserver重新累计问题

[yiminglian]

WITH t AS(
SELECT '1' dh ,22 qty
UNION
SELECT '1' ,33 
UNION
SELECT '1',7
UNION
SELECT '1',80
UNION
SELECT '2',55
UNION
SELECT '2',66
),
b AS (
SELECT'1' dh,'83'M
UNION
SELECT '2' dh,'70'm
)
SELECT t.dh,
       t.qty,SUM(qty)OVER (PARTITION BY t.dh ORDER BY t.dh  ROWS   UNBOUNDED PRECEDING),b.M FROM t LEFT JOIN b ON b.dh = t.dh

dh qty (无列名)    M
1    7    7           83
1    22    29       83
1    33    62       83
1    80    142     83
2    55    55       70
2    66    121    70

如何做到按M列的值重新累计 比如第四行累计后是142超过83要重新累计结果就80 如果下方有一行qty 是2 累计成82 如果是qty 是 4就不能累计了 最后一行同样累计结果大于70 需要重新累计成66

[yiminglian]

@锟斤拷锟斤拷 好的非常感谢

[锟斤拷锟斤拷]

@yiminglian 在开窗函数中判断暂时没什么思路。另外这张表需要一个id，我现在是按qty从小到大排序的，如果需要顺序最好加个id，然后把27行的qty换成id

[yiminglian]

@锟斤拷锟斤拷 我那种分组方式有办法简化代码么

[锟斤拷锟斤拷]

; 
WITH t AS(
SELECT '1' dh ,22 qty
UNION
SELECT '1' ,33 
UNION
SELECT '1',7
UNION
SELECT '1',80
UNION
SELECT '2',55
UNION
SELECT '2',66
),
b AS (
SELECT'1' dh,'83'M
UNION
SELECT '2' dh,'70'm
),
T1 AS (
SELECT t.dh,
       t.qty,
       ---用不到这一行SUM(qty)OVER (PARTITION BY t.dh ORDER BY t.dh  ROWS   UNBOUNDED PRECEDING),
       b.M 
       FROM t LEFT JOIN b ON b.dh = t.dh),
T2 AS (
SELECT *,ROW_NUMBER() OVER(PARTITION BY dh ORDER BY qty) RN FROM T1
),
RES AS (
SELECT * FROM T2 WHERE RN = 1
UNION ALL
SELECT A.dh,CASE WHEN A.qty+B.qty>B.M THEN A.qty ELSE A.qty+B.qty END,A.M,A.RN FROM T2 A,RES B WHERE A.RN = B.RN+1 AND A.dh = B.dh
)
SELECT * FROM RES ORDER BY DH,RN