[原]SQL中获得序列的方法

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

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

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

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

 

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

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 伪列：

select rownum seq from user_objects where rownum<10;

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

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

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

或者

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

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

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

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

 

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

select level seq from dual connect by level<=10

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

------------------------------------------------------------------------------
| 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 如命，吹毛求疵者可谓居家旅行，清热解毒，杀人越货的必备良药 ^_^