数据汇总问题

[mqmmx]

数据表内容如下
id location qty
a 1 100
a 2 50
b 2 100
c 1 200
a 3 100
......

现我要得到id = 'a' 数量(qty)为150(此数量可变)的内容明细如何写语句

结果为
id location qty
a 1 100
a 2 50

不用循环如何写

[caixia615]

题意说的不清...
select * from tablename where id='a' and qty=150 ???

[realx]

select * from tab where id in (
select id from tab group by id having sum(qty)=150 and id='a')

[tgm78]

select * from tab where in (
select id from tab group by id having sum(qty)=150 and id='a')

[jacalwu]

select id,location ,sum(qty)
from tab
where id='a'
group by id,location
having sum(qty)=150

[jacalwu]

嘻嘻 ，理解错了，搂上的sql 才对

[mqmmx]

qty=150是汇总数,可能不是150这么巧,
可能只是180那样结果就会变成
id location qty
a 1 100
a 3 100
两个中任何一个100中得个80和前一个相加就行了


上面表中的内容是库存,要求的qty是需要出库的数量,我要得到的明细是>=出库数量的记录明细,当然大于的话只能是多一条记录而已,
可能有几十万条记录满足条件,我要求的是那个正好满足条件或大于一条记录的汇总明细

[wiler]

select * from (
select id, location,qty,
sum(qty) over (order by id, location) snum --连续求和, 按照OVER后边内容汇总求和
from tab
where id='a'
)
where snum=150

[armorking2003]

select * from (
select id, location,qty,
sum(qty) over (PARTITION BY id, location) snum --连续求和, 按照OVER后边内容汇总求和
from tab
where id='a'
)
where snum=150