求一SQL存储过程
有两个表一表结构如下:
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)
求得到如下结果或一张临时表:
djbhspidhwphshl
dj0001 sp0001 hw001ph00150
dj0001 sp0001 hw001ph00240
dj0001 sp0001 hw002ph00290
dj0001 sp0001 hw003ph003120
dj0001 sp0002 hw001ph00690
dj0001 sp0002 hw002ph00910
意思就是需要根据表一中的spid和shl从表二中分摊出带批号和货位的数量,求高手
[解决办法]
;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 行受影响)*/
[解决办法]
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
[解决办法]
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*/