SQL server 数据分配算法

Question

表的明细中，分别有多笔记录（A 有4笔，B有3笔），比如A有一个分配总数量（100,1000,2000不等），按序号（0001,0002等）依次分配。如果总数量超过A的汇总数量，则多余的数量，叠加到最后一笔

Create table #car_parts(docno nvarchar(30),itemno nvarchar(10),pcode nvarchar(30),qty numeric(18,4) )
Insert #car_parts
select  'PO2021013','0001','A001',100 union all
select  'PO2021013','0002','A001',200 union all
select  'PO2021013','0003','A001',300 union all
select  'PO2021013','0004','B001',400 union all
select  'PO2021013','0005','B001',500 union all
select  'PO2021013','0006','A001',100 union all
select  'PO2021013','0007','B001',200

比如A的分配总数量为500，则分配如下
A 0001,100 -->100
A 0002,200 -->200
A 0003,300 -->200

比如A的分配总数量为800，则分配如下
A 0001,100 -->100
A 0002,200 -->200
A 0003,300 -->300
A 0006,300 -->200

比如A的分配总数量为1000，则分配如下
A 0001,100 -->100
A 0002,200 -->200
A 0003,300 -->300
A 0006,300 -->400

求算法

Answer 1

--是这样吗？
IF OBJECT_ID('tempdb..#car_parts') IS NOT NULL 
    DROP TABLE #car_parts
GO 

IF OBJECT_ID('tempdb..#tmpv') IS NOT NULL 
    DROP TABLE  #tmpv
GO 
IF OBJECT_ID('tempdb..#t') IS NOT NULL 
    DROP TABLE  #t
GO 

Create table #car_parts(docno nvarchar(30),itemno nvarchar(10),pcode nvarchar(30),qty numeric(18,4) )
Insert #car_parts
select  'PO2021013','0001','A001',100 union all
select  'PO2021013','0002','A001',200 union all
select  'PO2021013','0003','A001',300 union all
select  'PO2021013','0004','B001',400 union all
select  'PO2021013','0005','B001',500 union all
select  'PO2021013','0006','A001',100 union all
select  'PO2021013','0007','B001',200


CREATE TABLE #t(code CHAR(2),cou INT)
INSERT #t SELECT 'A',1000
UNION ALL SELECT 'B',1300


SELECT *,
(SELECT SUM(qty) FROM #car_parts AS cp2 WHERE LEFT(cp2.pcode,1)= LEFT(cp.pcode,1) AND cp2.itemno<=cp.itemno ) AS s ,
(SELECT SUM(qty) FROM #car_parts WHERE  LEFT(pcode,1)= LEFT(cp.pcode,1)) AS ss
INTO #tmpv
FROM #car_parts AS cp 
INNER JOIN #t AS t 
ON LEFT(cp.pcode,1)= t.code 
ORDER BY docno,cp.pcode,cp.itemno

SELECT docno,itemno,pcode,qty,cou,
CASE WHEN s > cou THEN qty-(s-cou)
    WHEN s<= cou AND s=ss THEN cou-s+qty
    ELSE qty
    END  VALUE
FROM #tmpv vv
WHERE itemno <=(SELECT TOP 1 itemno FROM #tmpv WHERE LEFT(pcode,1) = LEFT(vv.pcode,1) AND s-vv.cou>=0 ORDER BY s-vv.cou)
OR s<cou 

--result
docno    itemno    pcode    qty    cou    VALUE
PO2021013    0001    A001    100.0000    1000    100.0000
PO2021013    0002    A001    200.0000    1000    200.0000
PO2021013    0003    A001    300.0000    1000    300.0000
PO2021013    0006    A001    100.0000    1000    400.0000
PO2021013    0004    B001    400.0000    1300    400.0000
PO2021013    0005    B001    500.0000    1300    500.0000
PO2021013    0007    B001    200.0000    1300    400.0000