用于查询连续号段的SQL

时间：2010-09-14 来源：布衣卿相

在网络上发现一个SQL，用于查询连续号段。自己修改了下，将其思路整理清晰。测试SQL如下：

With tempTable As(
  select 2014 code,'00000001' tel from dual union all
  select 2014 code,'00000002' tel from dual union all
  select 2014 code,'00000003' tel from dual union all
  select 2014 code,'00000004' tel from dual union all
  select 2014 code,'00000005' tel from dual union all
  select 2014 code,'00000007' tel from dual union all
  select 2014 code,'00000008' tel from dual union all
  select 2014 code,'00000009' tel from dual union all
  select 2013 code,'00000120' tel from dual union all
  select 2013 code,'00000121' tel from dual union all
  select 2013 code,'00000122' tel from dual union all
  select 2013 code,'00000124' tel from dual union all
  select 2013 code,'00000125' tel from dual
),
group_tempTable As(
  Select a.*, a.tel - Rownum 分组
    From (Select *
            From tempTable
           Order By code, tel) a
)
Select b.code, Min(b.tel) Start_Tel, Max(b.tel) End_Tel
  From group_tempTable b
Group By b.code, b.分组
Order By b.code, b.分组

执行结果：

     CODE    START_TEL    END_TEL
1    2013    00000120    00000122
2    2013    00000124    00000125
3    2014    00000001    00000005
4    2014    00000007    00000009

相关阅读更多 +