我有一个表结构,
f1,f2
1,00000001
1,00000002
1,00000003
1,00000004
1,00000005
1,00000007
1,00000008
1,00000009
2,00000120
2,00000121
2,00000122
2,00000124
2,00000125怎么得到下面的结果
2,00000001,00000005
2,00000009,00000007
1,00000120,00000122
1,00000124,00000125
f1,f2
1,00000001
1,00000002
1,00000003
1,00000004
1,00000005
1,00000007
1,00000008
1,00000009
2,00000120
2,00000121
2,00000122
2,00000124
2,00000125怎么得到下面的结果
2,00000001,00000005
2,00000009,00000007
1,00000120,00000122
1,00000124,00000125
select 1 f1,'00000001' f2 from dual union all
select 1 f1,'00000002' f2 from dual union all
select 1 f1,'00000003' f2 from dual union all
select 1 f1,'00000004' f2 from dual union all
select 1 f1,'00000005' f2 from dual union all
select 1 f1,'00000007' f2 from dual union all
select 1 f1,'00000008' f2 from dual union all
select 1 f1,'00000009' f2 from dual union all
select 2 f1,'00000120' f2 from dual union all
select 2 f1,'00000121' f2 from dual union all
select 2 f1,'00000122' f2 from dual union all
select 2 f1,'00000124' f2 from dual union all
select 2 f1,'00000125' f2 from dual)select f1,min(f2)||','||max(f2) f2 from tt t
start with not exists(select 1 from tt where f1=t.f1 and f2=to_char(t.f2-1,'fm00000000'))
connect by prior f1=f1
and f2=prior to_char(f2+1,'fm00000000')
group by f1,rownum-level
1,00000009,00000007
2,00000120,00000122
2,00000124,00000125---------------------------------
with t1 as
(
select 1 f1, '00001' f2 from dual
union all
select 1 f1, '00002' f2 from dual
union all
select 1 f1, '00004' f2 from dual
union all
select 1 f1, '00005' f2 from dual
union all
select 2 f1, '00007' f2 from dual
union all
select 2 f1, '00008' f2 from dual
union all
select 2 f1, '00011' f2 from dual
union all
select 2 f1, '00012' f2 from dual
)
SELECT b.f1, min(b.f2) begin_hm, min(b.f2) end_hm
FROM (SELECT a.*, TO_NUMBER(a.f2 - ROWNUM) cc
FROM (SELECT * FROM t1 ORDER BY f1, f2) a) b
GROUP BY b.f1, b.cc
order by b.f1,start_hm
with tt as(
select 1 f1,'00000001' f2 from dual union all
select 1 f1,'00000002' f2 from dual union all
select 1 f1,'00000003' f2 from dual union all
select 1 f1,'00000004' f2 from dual union all
select 1 f1,'00000005' f2 from dual union all
select 1 f1,'00000007' f2 from dual union all
select 1 f1,'00000008' f2 from dual union all
select 1 f1,'00000009' f2 from dual union all
select 2 f1,'00000120' f2 from dual union all
select 2 f1,'00000121' f2 from dual union all
select 2 f1,'00000122' f2 from dual union all
select 2 f1,'00000124' f2 from dual union all
select 2 f1,'00000125' f2 from dual)
SELECT f1, MIN(f2) || ',' || MAX(f2)
FROM (SELECT f2 - rownum gn, a.* FROM (SELECT * FROM tt ORDER BY f2) a) b
GROUP BY f1, b.gn
ORDER BY f1