sql查询语句问题- sum求和的值作为查询条件

   

在物流系统开发过程中碰到这样一个问题，项目组的人讨论了很久用了这样的解决方法，

肯定还有地方需要优化，我先把我们的思路写出来，哪位朋友比较感兴趣，帮忙优化一下。

 

某储位档是已经按照优先级别排序的集合，字段有[储位代码(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	2

rID	locationcode	pilenum	level
1	T-E03-2	34	1
2	T-E03-3	50	1
3	T-E03-6	50	1