有如下的資料:
C_DATE NO
20070501 1
20070501 2
20070501 3
20070501 4
20070501 6
20070501 8
20070501 9
20070502 1
20070502 2
=====================
我現在想要得到如下的結果:
C_DATE Min_NO Max_NO
20070501 1 4
20070501 6 6
20070501 8 9
20070502 1 2
=====================
就是說　如果NO不連續了就換行。請問有好的方法碼？？

select c_date,
nvl(lag(e) over(partition by c_date order by s), minn) min_no,
nvl(S, maxn) max_no
from (select c_date,
lag(no, 1) over(partition by c_date order by no) S,
no E,
min(no) over(partition by c_date) minn,
max(no) over(partition by c_date) maxn
from test)
where nvl(E - S - 1, 1) <> 0;