数据表内容如下
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 不用循环如何写
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 不用循环如何写
select * from tablename where id='a' and qty=150 ???
select id from tab group by id having sum(qty)=150 and id='a')
select id from tab group by id having sum(qty)=150 and id='a')
from tab
where id='a'
group by id,location
having sum(qty)=150
可能只是180那样结果就会变成
id location qty
a 1 100
a 3 100
两个中任何一个100中得个80和前一个相加就行了
上面表中的内容是库存,要求的qty是需要出库的数量,我要得到的明细是>=出库数量的记录明细,当然大于的话只能是多一条记录而已,
可能有几十万条记录满足条件,我要求的是那个正好满足条件或大于一条记录的汇总明细
select id, location,qty,
sum(qty) over (order by id, location) snum --连续求和, 按照OVER后边内容汇总求和
from tab
where id='a'
)
where snum=150
select id, location,qty,
sum(qty) over (PARTITION BY id, location) snum --连续求和, 按照OVER后边内容汇总求和
from tab
where id='a'
)
where snum=150