斷號查詢問題

看書而得

[@more@]

SQL> select * from hyf_t1 order by id
2 ;

ID
-------
100
101
102
103
104
106
107
108
109
112
113
114
117
118

14 rows selected

SQL>
SQL> select * from (select id+1 begin,
2 sum( id) over(order by id rows between 0 preceding and 1 following) - id-1

end
3 ,sum( id) over(order by id rows between 0 preceding and 1 following) - id-

id-1 bb
4 from hyf_t1 ) where bb>0
5 /

BEGIN END BB
---------- ---------- ----------
105 105 1
110 111 2
115 116 2

這樣也行:
SQL> select * from (
2 select id+1 begin ,
3 lead(id,1,0) over(order by id )-1 end,
4 lead(id,1,0) over(order by id )- id-1 bb
5 from hyf_t1 ) where bb>0
6 /

BEGIN END BB
---------- ---------- ----------
105 105 1
110 111 2
115 116 2

SQL>