如下
表 A
--------------------------
COL1 COL2 COL3 COL4 COL5
QQ 102
QQ 103
QQ 104
QQ 105
QQ 108
QQ 109
PP 180
PP 182
PP 183
.......---------------------------
可不可以用SQL语句或ORACLE分析函数实现如下的查询
COL1 COL2 COL3 COL4 COL5
QQ 102 105 4
QQ 108 109 2
PP 180 180 1
PP 182 183 2
...........
--------------------------------------
注:COL3,COL4是每个连续段起始和结束,COL5是这个范围的数量!
也可以用比较简单的存储过程实现! 小弟谢谢各位!
表 A
--------------------------
COL1 COL2 COL3 COL4 COL5
QQ 102
QQ 103
QQ 104
QQ 105
QQ 108
QQ 109
PP 180
PP 182
PP 183
.......---------------------------
可不可以用SQL语句或ORACLE分析函数实现如下的查询
COL1 COL2 COL3 COL4 COL5
QQ 102 105 4
QQ 108 109 2
PP 180 180 1
PP 182 183 2
...........
--------------------------------------
注:COL3,COL4是每个连续段起始和结束,COL5是这个范围的数量!
也可以用比较简单的存储过程实现! 小弟谢谢各位!
create table test
(a varchar2(2),
b varchar2(10))
select * from test
qq 101
qq 102
qq 103
qq 104
qq 108
qq 109
pp 104
pp 105
pp 109
pp 110
-------------------------------------
select a,mi,ma,ma-mi+1 from
(
select distinct t2.a,case when t2.b<t2.d then (select min(t1.b) from test t1 where t1.a=t2.a and t1.b<t2.d)
when t2.b>t2.d then (select min(t1.b) from test t1 where t1.a=t2.a and t1.b>t2.d) end as mi,
case when t2.b<t2.d then (select max(t1.b) from test t1 where t1.a=t2.a and t1.b<=t2.d)
when t2.b>t2.d then (select max(t1.b) from test t1 where t1.a=t2.a and t1.b>t2.d) end as ma
from
(
select a1.a,a1.b,b1.a as c,b1.b as d
from test a1,
(
select a.a,a.b from
( select a,b,ROW_NUMBER() over(partition by a order by b) as cn from test) a,
( select a,b,ROW_NUMBER() over(partition by a order by b) as cn from test) b
where a.a=b.a and a.cn=b.cn-1 and b.b-a.b<>1)b1
where a1.a=b1.a
order by a1.a,a1.b) t2)
where mi is not null
-----------------------
pp 104 105 2
pp 109 111 3
qq 101 104 4
qq 108 109 2
from (
select col1,col2,row_number()over(partition by col1 order by col2) rn
from a )
group by col1,rn-col2
;
insert into test values('QQ',102);
insert into test values('QQ',103);
insert into test values('QQ',104);
insert into test values('QQ',105);
insert into test values('QQ',108);
insert into test values('QQ',109);
insert into test values('PP',180);
insert into test values('PP',182);
insert into test values('PP',183);
COMMIT;
SELECT * from test t;
select col1, min(col2), max(col2), count(*)
from (select col1,
col2,
row_number() over(partition by col1 order by col2) rn
from test order by col2)
group by col1, rn - col2