oracle中如何处理结果集合并？

Question

我现在查询出来的结果集如上图所示。我想在oracle查询的结果集中，重新将startNo和endNo连续的票号给合并起来。例如我圈起来的那个段票号为：
01016680~01018000和010119937~010200000

这个应该怎么去实现？

Answer 1

假设表的名字叫T_CONNECTselect min(startno) || '~' || max(endno), count(*)from (&nbsp; -- 查出号码连续记录段的第一个号码&nbsp; select t.*, CONNECT_BY_ROOT(startno) rootno&nbsp; from t_connect t&nbsp; start with startno in (&nbsp; &nbsp; --查出不连续号码的第一条记录，作为递归查询的初始条件&nbsp; &nbsp; select startno&nbsp; &nbsp; from (&nbsp; &nbsp; &nbsp; select startno, endno, lag(endno) over (order by startno) prev_endno&nbsp; &nbsp; &nbsp; from t_connect t&nbsp; &nbsp; &nbsp; order by startno&nbsp; &nbsp; )&nbsp; &nbsp; where (startno <> prev_endno + 1) or prev_endno is null&nbsp; )&nbsp; connect by prior endno + 1 = startno)group by rootnoorder by rootno