oracle中
表:
bookno invono status sums
1 01 01 0
1 02 01 1
1 03 01 1
1 04 02 1
1 05 01 1
1 06 01 0
2 001 01 1
2 002 01 1想得到的结果是。如果status相同,且在同一bookno下invono号码连续就合并为一条记录,如果不连续就不合并 如下
如下
bookno startno endno 数量 status sums
1 01 03 3 01 2
1 04 04 1 02 1
1 05 06 2 01 1
2 001 002 2 01 2还请高人指点 在线等
表:
bookno invono status sums
1 01 01 0
1 02 01 1
1 03 01 1
1 04 02 1
1 05 01 1
1 06 01 0
2 001 01 1
2 002 01 1想得到的结果是。如果status相同,且在同一bookno下invono号码连续就合并为一条记录,如果不连续就不合并 如下
如下
bookno startno endno 数量 status sums
1 01 03 3 01 2
1 04 04 1 02 1
1 05 06 2 01 1
2 001 002 2 01 2还请高人指点 在线等
create table ta (bookno integer, invono varchar2(3), status varchar2(3), sums integer);truncate table ta;
insert into ta
values( 1,'01','01',0 );
insert into ta
values( 1,'02','01',1 );
insert into ta
values( 1,'03','01',1 );
insert into ta
values( 1,'04','02',1 );
insert into ta
values( 1,'05','01',1 );
insert into ta
values( 1,'06','01',0 );
insert into ta
values( 2,'001','01',1 );
insert into ta
values( 2,'002','01',1 );
commit;select bookno,
min(invono) as starno,
max(invono) endno,
count(1) cnt,
status,
sum(sums)
from (select bookno,
invono,
status,
sums,
rn - (row_number() over(order by bookno, invono)) as rn1
from (select bookno,
invono,
status,
sums,
row_number() over(PARTITION BY bookno, status order by bookno, status, invono) as rn
from ta))
group by bookno, status, rn1;