select fphm,min(kshm),max(kshm) from table group by fphm having (max(kshm) - min(kshm) + 1) = count(fphm) ------------------------------ 你这句话我测试过了,不对
给你看这个你就明白了! 建表: create table tt ( a number(20) ); 插入数据: 1,2,3,4,5,7,8,9,11,12,23 查询语句: SELECT A.A || B.A FROM (SELECT ROWNUM R, A || '-' A FROM (SELECT A A FROM TTTT MINUS SELECT A + 1 A FROM TTTT)) A, (SELECT ROWNUM R, A FROM (SELECT A A FROM TTTT MINUS SELECT A - 1 A FROM TTTT)) B WHERE A.R = B.R执行结果: A1 T4.A2-1 ---------- ---------- 1 5 7 9 11 12 23 23
我的比较复杂,你看看,呵呵呵,我也是初学,搞了好久; 第一步:create table atest1 ( yh VARCHAR2(20) aa VARCHAR2(20) yhaa number ); insert into atest1 select t.fphm,t.kshm,t.aayh from (select fphm,kshm,rownum aayh from 你的表order by fphm desc,kshm) t //目的: 确保数据排列书序,以及给行赋一个序列号; 第二步:create table atest2(yh VARCHAR2(20)) insert into atest2 select distinct fphm from 你的表 order by fphm desc; 第三步:存贮过程:
第二不是为了以后过程要用的,可能有更好的办法,呵呵呵呵 第三步:存贮过程: create or replace procedure sp_result0427 is iCount1 int:=0; iCount2 int:=0; bhid number:=0; zzaa varchar2(20) ; cursor custcur1 is select yh,aa,yhaa from aabhaa; cursor custcur is select distinct yh from atest2 order by yh desc; tmpyh atest1.yh%type; tmpaa atest1.aa%type; tmpyhaa atest1.yhaa%type; tmpzyh atest2.yh%type; begin open custcur; fetch custcur into tmpzyh; loop exit when not custcur%FOUND; select aa into zzaa from atest1 where yh = tmpzyh and yhaa = ( select min(yhaa) from atest1 where yh = tmpzyh); select max(yhaa) into bhid from atest1 where yh = tmpzyh; open custcur1; fetch custcur1 into tmpyh,tmpaa,tmpyhaa; loop exit when not custcur1%FOUND; if (tmpyh =tmpzyh) then ---if1 begin select to_number(aa) into iCount1 from atest1 where yhaa = tmpyhaa ; if(tmpyhaa < bhid) then --if2 begin select to_number(aa) into iCount2 from atest1 where yhaa = tmpyhaa + 1 ; iCount1 := iCount1 + 1; if (iCount1 != iCount2) then --if3 begin dbms_output.put_line(tmpyh ||' '||zzaa||' '||tmpaa); select aa into zzaa from atest1 where yhaa = tmpyhaa + 1; end; end if;--if3 end; else if (tmpyhaa = bhid) then dbms_output.put_line(tmpyh ||' '||zzaa||' '||tmpaa); end if; end if;--if2
end; end if; --if1 fetch custcur1 into tmpyh,tmpaa,tmpyhaa; end loop; close custcur1; fetch custcur into tmpzyh; end loop; close custcur; commit; end sp_result0427;
cursor custcur1 is select yh,aa,yhaa from aabhaa;应该为cursor custcur1 is select yh,aa,yhaa from atest1
已经搞定了 itpub上的高手: SQL> select * from gap;ID SEQ ---------- ---------- 1 1 1 4 1 5 1 8 2 1 2 2 2 9 select res1.id, res2.seq str, res1.seq end from (select rownum rn, c.* from (select * from gap a where not exists (select null from gap b where b.id = a.id and a.seq = b.seq - 1) order by id, seq) c ) res1, (select rownum rn, d.* from (select * from gap a where not exists (select null from gap b where b.id = a.id and a.seq = b.seq + 1) order by id, seq) d ) res2 where res1.id = res2.id and res1.rn = res2.rn /ID STR END --------- ---------- ---------- 1 1 1 1 4 5 1 8 8 2 1 2 2 9 9
select id,min(seq),max(seq) from ( select id,seq, seq-row_number() over(order by seq) diff from gap ) group by id,diff
from table group by fphm having (max(kshm) - min(kshm) + 1) = count(fphm)
------------------------------
你这句话我测试过了,不对
建表:
create table tt
(
a number(20)
);
插入数据:
1,2,3,4,5,7,8,9,11,12,23
查询语句:
SELECT A.A || B.A
FROM (SELECT ROWNUM R, A || '-' A
FROM (SELECT A A
FROM TTTT
MINUS
SELECT A + 1 A FROM TTTT)) A,
(SELECT ROWNUM R, A
FROM (SELECT A A
FROM TTTT
MINUS
SELECT A - 1 A FROM TTTT)) B
WHERE A.R = B.R执行结果:
A1 T4.A2-1
---------- ----------
1 5
7 9
11 12
23 23
第一步:create table atest1
(
yh VARCHAR2(20)
aa VARCHAR2(20)
yhaa number
);
insert into atest1 select t.fphm,t.kshm,t.aayh from (select fphm,kshm,rownum aayh from 你的表order by fphm desc,kshm) t
//目的: 确保数据排列书序,以及给行赋一个序列号;
第二步:create table atest2(yh VARCHAR2(20))
insert into atest2 select distinct fphm from 你的表 order by fphm desc;
第三步:存贮过程:
第三步:存贮过程:
create or replace procedure sp_result0427 is
iCount1 int:=0;
iCount2 int:=0;
bhid number:=0;
zzaa varchar2(20) ;
cursor custcur1 is select yh,aa,yhaa from aabhaa;
cursor custcur is select distinct yh from atest2 order by yh desc;
tmpyh atest1.yh%type;
tmpaa atest1.aa%type;
tmpyhaa atest1.yhaa%type;
tmpzyh atest2.yh%type;
begin
open custcur;
fetch custcur into tmpzyh;
loop
exit when not custcur%FOUND;
select aa into zzaa from atest1 where yh = tmpzyh and yhaa = (
select min(yhaa) from atest1 where yh = tmpzyh);
select max(yhaa) into bhid from atest1 where yh = tmpzyh;
open custcur1;
fetch custcur1 into tmpyh,tmpaa,tmpyhaa;
loop
exit when not custcur1%FOUND;
if (tmpyh =tmpzyh) then ---if1
begin
select to_number(aa) into iCount1 from atest1 where yhaa = tmpyhaa ;
if(tmpyhaa < bhid) then --if2
begin
select to_number(aa) into iCount2 from atest1 where yhaa = tmpyhaa + 1 ;
iCount1 := iCount1 + 1;
if (iCount1 != iCount2) then --if3
begin
dbms_output.put_line(tmpyh ||' '||zzaa||' '||tmpaa);
select aa into zzaa from atest1 where yhaa = tmpyhaa + 1;
end;
end if;--if3
end;
else if (tmpyhaa = bhid) then
dbms_output.put_line(tmpyh ||' '||zzaa||' '||tmpaa);
end if;
end if;--if2
end;
end if; --if1
fetch custcur1 into tmpyh,tmpaa,tmpyhaa;
end loop;
close custcur1;
fetch custcur into tmpzyh;
end loop;
close custcur;
commit;
end sp_result0427;
SQL> select * from gap;ID SEQ
---------- ----------
1 1
1 4
1 5
1 8
2 1
2 2
2 9
select res1.id, res2.seq str, res1.seq end
from
(select rownum rn, c.* from (select * from gap a where not exists (select null from gap b where b.id = a.id and a.seq = b.seq - 1) order by id, seq) c ) res1,
(select rownum rn, d.* from (select * from gap a where not exists (select null from gap b where b.id = a.id and a.seq = b.seq + 1) order by id, seq) d ) res2
where res1.id = res2.id
and res1.rn = res2.rn
/ID STR END
--------- ---------- ----------
1 1 1
1 4 5
1 8 8
2 1 2
2 9 9
from
(
select id,seq, seq-row_number() over(order by seq) diff
from gap
)
group by id,diff
这个太好了,又简单又好啊