SQL中获得序列的方法

这里说的“序列”不是 Oracle 中的 sequence ，而是产生一个从 n 到 m 的序列表，如下所示：

?

1 2 3 4 5 6 7 8 9 10 11 12

SEQ ----------           1           2           3           4           5           6           7           8           9          10

序列表有什么用处呢，用处可大了，以后的文章中将大量用到。

我们先从产生 1 到 m 的序列表开始吧。

 

第一种做法是先建一个表，然后循环插入：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

create table t_seq (    seq int );    declare     i number; begin    for i in 1..10 loop      insert into t_seq values ( i );    end loop;    commit ; end ;

这种做法的可移植性非常好，几乎任何数据库都可以使用，即使某些 DBMS 不支持 for 循环（例如SQL Server），完全可以借助其他循环语句例如 while 实施。

 

第二种，借助 rownum 伪列：

?

1	select rownum seq from user_objects where rownum<10;

通常我们借助一个记录比较多的系统表来获得 rownum 。

对于没有类似 rownum 这项功能的SQL Server，可以借助 identity 的递增来实现：

?

1 2	select seq=identity( int ,1,1) into #t from sys.objects ; select seq from #t where seq<=10;

或者

?

1 2	select top 10 seq=identity( int ,1,1) into #t from sys.objects ; select seq from #t;

或者借助分析函数 row_number() 实现：

?

1	select top 10 row_number() over ( order by object_id) from sys.objects;

当然实现的方式有很多，但是这种方法产生的序列受限于所借助的表，产生一个很大的序列就比较头痛了，而且产生I/O损耗是不可避免的。可以总结为直观，可移植性不佳。

 

第三种方法，递归查询，暂时发现能在 Oracle 10g 以上使用：

?

1	select level seq from dual connect by level <=10

这条语句一个 I/O 都不产生：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

------------------------------------------------------------------------------ | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 | |   1 |  SORT AGGREGATE               |      |     1 |            |          | |   2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          | |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------       Statistics ----------------------------------------------------------            0  recursive calls            0  db block gets            0  consistent gets            0  physical reads            0  redo size          508  bytes sent via SQL*Net to client          469  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            1  sorts (memory)            0  sorts (disk)            1  rows processed

该种方法对惜 I/O 如命，吹毛求疵者可谓居家旅行，清热解毒，杀人越货的必备良药 ^_^