断号查询问题
看书而得
[@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>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/703656/viewspace-973987/ ，如需转载，请注明出处，否则将追究法律责任。
-
博文量127
-
访问量855003
