select c.rownum ,sum(a.qty) from table1 a, (select 0 rownum,0 id,0 qty from dual union all select rownum, id,qty from table1 where qty=0 union all select (select count(id) from table1 where qty=0)+1 rownum, max(id) +1 id,0 qty from table1) b, (select 0 rownum,0 id,0 qty from dual union all select rownum, id,qty from table1 where qty=0 union all select (select count(id) from table1 where qty=0)+1 rownum, max(id) +1 id,0 qty from table1) c, where a.id between a.id and c.id and b.rownum=c.rownum-1;
select c.num,sum(a.qty) from table1 a, (select 0 num,0 id,0 qty from dual union all select rownum num, id,qty from table1 where qty=0 union all select (select count(id) from table1 where qty=0)+1 num, (select max(id) +1 id from table1),0 qty from dual) b, (select 0 num,0 id,0 qty from dual union all select rownum num, id,qty from table1 where qty=0 union all select (select count(id) from table1 where qty=0) + 1 num, (select max(id) +1 id from table1),0 qty from dual) c where a.id between b.id and c.id and b.num=c.num-1 group by c.num; 这个应该可以了 ------------------------------------------------ SQL> select c.num,sum(a.qty) from table1 a, 2 (select 0 num,0 id,0 qty from dual 3 union all 4 select rownum num, id,qty from table1 where qty=0 5 union all 6 select (select count(id) from table1 where qty=0)+1 num, 7 (select max(id) +1 id from table1),0 qty from dual) b, 8 (select 0 num,0 id,0 qty from dual 9 union all 10 select rownum num, id,qty from table1 where qty=0 11 union all 12 select (select count(id) from table1 where qty=0) + 1 num, 13 (select max(id) +1 id from table1),0 qty from dual) c 14 where a.id between b.id and c.id and b.num=c.num-1 group by c.num;NUM SUM(A.QTY) --- ---------- 1 58 2 35 3 20
我觉得可以增加一个临时字段alter table table1 add (group_id number(4));update table1 a set group_id = ( select min(id) from table1 b where b.qty = 0 and a.id < b.id);update table1 a set group_id = (select max(id) from table1 b) where a.group_id is null;commit;select sum(QTY) from table1 group by group_id
select a.G,( select sum( QTY )from table1 t where t.ID between a.ida and a.idb )S from ( select row_number()over(order by ID) G, ID ida, lead(ID,1,99999)over(order by ID) idb from ( select 0 ID from dual unoin all select ID from table1 a where a.qty=0 )a )awhere t.id<= a.id
select a.G,( select sum( QTY )from table1 t where t.ID between a.ida and a.idb )S from ( select row_number()over(order by ID) G, ID ida, lead(ID,1,99999)over(order by ID) idb from ( select 0 ID from dual unoin all select ID from table1 a where a.qty=0 )a )a ; 手误,多加了 --where t.id<= a.id
或者 SQL> select c.num||','||sum(a.qty) from table1 a, 2 (select 0 num,0 id,0 qty from dual 3 union all 4 select rownum num, id,qty from table1 where qty=0 5 union all 6 select (select count(id) from table1 where qty=0)+1 num, 7 (select max(id) +1 id from table1),0 qty from dual) b, 8 (select 0 num,0 id,0 qty from dual 9 union all 10 select rownum num, id,qty from table1 where qty=0 11 union all 12 select (select count(id) from table1 where qty=0) + 1 num, 13 (select max(id) +1 id from table1),0 qty from dual) c 14 where a.id between b.id and c.id and b.num=c.num-1 group by c.num;C.NUM||','||SUM(A.QTY) -------------------------------------------------------------------------------- 1,58 2,35 3,20 不知道你要哪种
(select 0 rownum,0 id,0 qty from dual
union all
select rownum, id,qty from table1 where qty=0
union all
select (select count(id) from table1 where qty=0)+1 rownum,
max(id) +1 id,0 qty from table1) b,
(select 0 rownum,0 id,0 qty from dual
union all
select rownum, id,qty from table1 where qty=0
union all
select (select count(id) from table1 where qty=0)+1 rownum,
max(id) +1 id,0 qty from table1) c,
where a.id between a.id and c.id and b.rownum=c.rownum-1;
(select 0 num,0 id,0 qty from dual
union all
select rownum num, id,qty from table1 where qty=0
union all
select (select count(id) from table1 where qty=0)+1 num,
(select max(id) +1 id from table1),0 qty from dual) b,
(select 0 num,0 id,0 qty from dual
union all
select rownum num, id,qty from table1 where qty=0
union all
select (select count(id) from table1 where qty=0) + 1 num,
(select max(id) +1 id from table1),0 qty from dual) c
where a.id between b.id and c.id and b.num=c.num-1 group by c.num;
这个应该可以了
------------------------------------------------
SQL> select c.num,sum(a.qty) from table1 a,
2 (select 0 num,0 id,0 qty from dual
3 union all
4 select rownum num, id,qty from table1 where qty=0
5 union all
6 select (select count(id) from table1 where qty=0)+1 num,
7 (select max(id) +1 id from table1),0 qty from dual) b,
8 (select 0 num,0 id,0 qty from dual
9 union all
10 select rownum num, id,qty from table1 where qty=0
11 union all
12 select (select count(id) from table1 where qty=0) + 1 num,
13 (select max(id) +1 id from table1),0 qty from dual) c
14 where a.id between b.id and c.id and b.num=c.num-1 group by c.num;NUM SUM(A.QTY)
--- ----------
1 58
2 35
3 20
select min(id) from table1 b where b.qty = 0 and a.id < b.id);update table1 a set group_id = (select max(id) from table1 b) where a.group_id is null;commit;select sum(QTY) from table1 group by group_id
select sum( QTY )from table1 t where t.ID between a.ida and a.idb
)S
from (
select row_number()over(order by ID) G,
ID ida, lead(ID,1,99999)over(order by ID) idb
from (
select 0 ID from dual unoin all
select ID from table1 a where a.qty=0
)a
)awhere t.id<= a.id
select sum( QTY )from table1 t where t.ID between a.ida and a.idb
)S
from (
select row_number()over(order by ID) G,
ID ida, lead(ID,1,99999)over(order by ID) idb
from (
select 0 ID from dual unoin all
select ID from table1 a where a.qty=0
)a
)a
;
手误,多加了
--where t.id<= a.id
SQL> select c.num||','||sum(a.qty) from table1 a,
2 (select 0 num,0 id,0 qty from dual
3 union all
4 select rownum num, id,qty from table1 where qty=0
5 union all
6 select (select count(id) from table1 where qty=0)+1 num,
7 (select max(id) +1 id from table1),0 qty from dual) b,
8 (select 0 num,0 id,0 qty from dual
9 union all
10 select rownum num, id,qty from table1 where qty=0
11 union all
12 select (select count(id) from table1 where qty=0) + 1 num,
13 (select max(id) +1 id from table1),0 qty from dual) c
14 where a.id between b.id and c.id and b.num=c.num-1 group by c.num;C.NUM||','||SUM(A.QTY)
--------------------------------------------------------------------------------
1,58
2,35
3,20
不知道你要哪种