with t as ( select 1 a, 15 b from dual union all select 2, 21 from dual union all select 3, 1 from dual union all select 4, 2 from dual union all select 5, 7 from dual union all select 6, 19 from dual union all select 7, 23 from dual )select * from t where a< ( select min(a) from ( select a,b,sum(b)over(order by a) rn from t ) where rn-50>0 ) order by a--result: 1 15 2 21 3 1 4 2 5 7
SELECT * FROM tb WHERE id <= (SELECT MIN(id) FROM (SELECT id,SUM(a) OVER(ORDER BY id) rn FROM tb) WHERE rn > 50)
select id,a from( select id,a,sum(a)over(order by id)s) where s-a<50
select id, a from (select id, a, sum(a) over(order by id) sumqty from tab) where sumqty > 50
with t as ( select 1 a, 15 b from dual union all select 2, 21 from dual union all select 3, 1 from dual union all select 4, 2 from dual union all select 5, 7 from dual union all select 6, 19 from dual union all select 7, 23 from dual ) select a,b from ( select a,b,sum(b)over(order by a) rn from t ) where rn<50
。。忘了写from select id,a from( select id,a,sum(a)over(order by id)s from tt) where s-a <50
select * from tab where id<= ( select min(id) from (select id, sum(a) over(order by id) sumqty from tab ) where sumqty>50 ) order by id asc
(
select 1 a, 15 b from dual
union all
select 2, 21 from dual
union all
select 3, 1 from dual
union all
select 4, 2 from dual
union all
select 5, 7 from dual
union all
select 6, 19 from dual
union all
select 7, 23 from dual
)select * from t where a<
(
select min(a) from
(
select a,b,sum(b)over(order by a) rn from t
) where rn-50>0
)
order by a--result:
1 15
2 21
3 1
4 2
5 7
SELECT * FROM tb WHERE id <= (SELECT MIN(id) FROM (SELECT id,SUM(a) OVER(ORDER BY id) rn FROM tb) WHERE rn > 50)
select id,a,sum(a)over(order by id)s)
where s-a<50
select id, a
from (select id, a, sum(a) over(order by id) sumqty from tab)
where sumqty > 50
(
select 1 a, 15 b from dual
union all
select 2, 21 from dual
union all
select 3, 1 from dual
union all
select 4, 2 from dual
union all
select 5, 7 from dual
union all
select 6, 19 from dual
union all
select 7, 23 from dual
) select a,b from
(
select a,b,sum(b)over(order by a) rn from t
) where rn<50
select id,a from(
select id,a,sum(a)over(order by id)s
from tt)
where s-a <50
select * from tab
where id<=
(
select min(id)
from (select id, sum(a) over(order by id) sumqty from tab )
where sumqty>50
)
order by id asc