有两个表一表结构如下:
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',50)
insert into sphwph values('sp0001','hw0001','ph002',40)
insert into sphwph values('sp0001','hw0002','ph002',90)
insert into sphwph values('sp0001','hw0003','ph003',500)
insert into sphwph values('sp0001','hw0004','ph005',1000)
insert into sphwph values('sp0002','hw0001','ph006',90)
insert into sphwph values('sp0002','hw0002','ph009',100)
求得到如下结果或一张临时表:
djbh spid hw ph shl
dj0001 sp0001 hw001 ph001 50
dj0001 sp0001 hw001 ph002 40
dj0001 sp0001 hw002 ph002 90
dj0001 sp0001 hw003 ph003 120
dj0001 sp0002 hw001 ph006 90
dj0001 sp0002 hw002 ph009 10
意思就是需要根据表一中的spid和shl从表二中分摊出带批号和货位的数量,求高手
------解决方案--------------------
- SQL code
;with TTas(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 N1from TT Binner join TT C on B.spid = C.spid and C.nn <=B.nngroup by B.spid,B.hw,b.ph,b.shl),T2 as (select B.spid,B.hw,b.ph,b.shlfrom djjx Ainner join T1 B on A.spid = B.spid and B.N1<A.shl)select *from T2union allselect distinct Z2.spid,Z2.hw,Z2.ph,Z.shl - (select SUM(shl) from T2 where spid = Z2.spid) as shlfrom (select B.spid,B.hw,b.ph,A.shlfrom djjx Ainner join T1 B on A.spid = B.spid and B.N1>A.shl) Zcross 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 50sp0001 hw0001 ph002 40sp0001 hw0002 ph002 90sp0002 hw0001 ph006 90sp0001 hw0003 ph003 120sp0002 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), --单据编号 keyspid varchar(20), --商品IDshl int --数量)--其中二行数据:insert into djjx values('dj0001','sp0001',300)insert into djjx values('dj0001','sp0002',100)--表二:create table sphwph (spid varchar(20), --商品ID keyhw varchar(20), --货位 keyph varchar(20), --批号 keyshl int --数量)其中几行数据:insert into sphwph values('sp0001','hw0001','ph001',50)insert into sphwph values('sp0001','hw0001','ph002',40)insert into sphwph values('sp0001','hw0002','ph002',90)insert into sphwph values('sp0001','hw0003','ph003',500)insert into sphwph values('sp0001','hw0004','ph005',1000)insert into sphwph values('sp0002','hw0001','ph006',90)insert into sphwph values('sp0002','hw0002','ph009',100)/*求得到如下结果或一张临时表:djbh spid hw ph shldj0001 sp0001 hw001 ph001 50dj0001 sp0001 hw001 ph002 40dj0001 sp0001 hw002 ph002 90dj0001 sp0001 hw003 ph003 120dj0001 sp0002 hw001 ph006 90dj0001 sp0002 hw002 ph009 10 */;with tas(select id=row_number()over(partition by a.spid order by getdate()),a.djbh,a.spid,a.shl as total,b.hw,b.ph,b.shl from djjx a inner join sphwph b on a.spid=b.spid),m as(select id,djbh,spid,(total-shl) as total,hw,ph,shl from t where id=1union allselect a.id,a.djbh,a.spid,b.total-a.shl,a.hw,a.ph,a.shl from t ainner join m b on a.id=b.id+1 and a.spid=b.spid)select djbh,spid,hw,ph,case when total<0 then shl-ABS(total) else shl end as shl from m where case when total<0 then shl-ABS(total) else shl end>0 order by spid,id/*djbh spid hw ph shldj0001 sp0001 hw0001 ph001 50dj0001 sp0001 hw0001 ph002 40dj0001 sp0001 hw0002 ph002 90dj0001 sp0001 hw0003 ph003 120dj0001 sp0002 hw0001 ph006 90dj0001 sp0002 hw0002 ph009 10*/