;with TT
as(select sphwph.*,ROW_NUMBER() over(partition by spid order by getdate()) as nn from sphwph),

T1 as(
select B.spid,B.hw,b.ph,b.shl,SUM(C.shl) as N1
from TT B
inner join TT C on B.spid = C.spid and C.nn <=B.nn
group by B.spid,B.hw,b.ph,b.shl),

T2 as (
select B.spid,B.hw,b.ph,b.shl
from djjx A
inner join T1 B on A.spid = B.spid and B.N1<A.shl)


select *
from T2
union all

select distinct Z2.spid,Z2.hw,Z2.ph,Z.shl - (select SUM(shl) from T2 where spid = Z2.spid) as shl
from (
select B.spid,B.hw,b.ph,A.shl
from djjx A
inner join T1 B on A.spid = B.spid and B.N1>A.shl) Z
cross apply(select top 1  Z1.spid,Z1.hw,Z1.ph,Z1.shl 
                from (select B.spid,B.hw,b.ph,b.shl
                        from djjx A
                            inner join T1 B on A.spid = B.spid 
                            and B.N1>A.shl) Z1 
                where Z1.spid = Z.spid 
                order by spid,hw,ph asc) Z2
                
                
/*
spid                 hw                   ph                   shl
-------------------- -------------------- -------------------- -----------
sp0001               hw0001               ph001                50
sp0001               hw0001               ph002                40
sp0001               hw0002               ph002                90
sp0002               hw0001               ph006                90
sp0001               hw0003               ph003                120
sp0002               hw0002               ph009                10

(6 行受影响)
*/

------解决方案--------------------

SQL code
with tableA as (
select b.*,a.hw,a.ph,a.shl as shla,rank() over(partition by a.spid order by hw,ph) as row from
 sphwph a join djjx b on a.spid=b.spid
) 
select y.djbh,y.spid,y.hw,y.ph, shl=(case when shl1>0 then y.shla else shl2 end) from (select *,
shl1=(select shl-sum(shla)from tableA a where a.spid=b.spid and a.row<=b.row group by shl),
shl2=(select shl-sum(shla)from tableA a where a.spid=b.spid and a.row<b.row group by shl)
 from  tableA b )y join tableA x on x.spid=y.spid and x.row=y.row 
where (case when shl1>0 then y.shla else shl2 end)>=0

------解决方案--------------------
SQL code

create table djjx
(
djbh varchar(20), --单据编号 key
spid varchar(20), --商品ID
shl int --数量
)
--其中二行数据:
insert into djjx values('dj0001','sp0001',300)
insert into djjx values('dj0001','sp0002',100)
--表二:
create table sphwph  
(
spid varchar(20), --商品ID key
hw varchar(20), --货位 key
ph varchar(20), --批号 key
shl int --数量
)
其中几行数据:
insert into sphwph values('sp0001','hw0001','ph001'