在物流系统开发过程中碰到这样一个问题,项目组的人讨论了很久用了这样的解决方法,
肯定还有地方需要优化,我先把我们的思路写出来,哪位朋友比较感兴趣,帮忙优化一下。
某储位档是已经按照优先级别排序的集合,字段有[储位代码(locationcode),储位产品存放数量(pilenum),优先级(level)]
现在要从该表中按照排好的顺序取出150个产品。如何最简单实现?
创建测试资料文件某储位档(test1)
the following is our sql script:
select identity(int,1,1) as rID,* into #test from test1
select * from #test
if not exists(select * from #test a where (select sum(pilenum) from #test where rID <= a.rID) <90)
begin
select top 1 * from #test
end
else
begin
select * from #test a where (select sum(pilenum) from #test where rID <= a.rID) < 90
union (select * from #test where rID = (select max(rID) from #test a where (select sum(pilenum) from #test where rID <= a.rID) < 90)+1)
end
drop table #test
executed result:
rID
locationcode
pilenum
level
1
T-E03-2
34
1
2
T-E03-3
50
1
3
T-E03-6
50
1
4
T-E03-7
12
1
5
T-E03-8
80
2
6
T-E04-5
30
3
7
T-E04-7
50
2rID
locationcode
pilenum
level
1
T-E03-2
34
1
2
T-E03-3
50
1
3
T-E03-6
50
1