1
2
3
4

create table t1(
  id int primary key,
  type varchar(1) not null
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

select sid,eid,type from 
(select id as sid,type,row_number() OVER () as rnum
   from t1
  where exists(select 1 from t1 as t1s
                where t1s.type=t1.type and t1s.id=t1.id+1)
and not exists(select 1 from t1 as t1s
                where t1s.type=t1.type and t1s.id=t1.id-1)) as t1s
inner join 
(select id as eid,row_number() OVER () as rnum
   from t1
  where exists(select 1 from t1 as t1e
                where t1e.type=t1.type and t1e.id=t1.id-1)
and not exists(select 1 from t1 as t1e
                where t1e.type=t1.type and t1e.id=t1.id+1)
) as t1e on t1s.rnum=t1e.rnum
union
select id,id,type from t1
 where not exists(select 1 from t1 as t1b
                   where t1b.type=t1.type and t1b.id=t1.id-1)
   and not exists(select 1 from t1 as t1a
                   where t1a.type=t1.type and t1a.id=t1.id+1)
order by 1