SQL 生成连续的数值

SQL 生成连续的数值，你希望有一个“行数据生成器”。如果你的查询里需要数据透视表，就用得着这个行数据生成器。例如，你想返回如下所示的结果集，并且可以为它指定任意数目的行数据。

SQL 生成连续的数值 问题描述

你希望有一个“行数据生成器”。如果你的查询里需要数据透视表，就用得着这个行数据生成器。例如，你想返回如下所示的结果集，并且可以为它指定任意数目的行数据。

ID
---
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
...

如果数据库提供了可以动态地生成行数据的内置函数，那么就不需要预先创建一个固定行数的数据透视表。这就是动态的行数据生成器如此有用的原因。否则，我们必须借助一个传统的、行数固定（也许并不够用）的数据透视表来生成所需的行数据。

SQL 生成连续的数值 解决方案

本解决方案展示如何返回从 1 开始递增至 10 的 10 行数据。我们也可以简单地改动一下代码，以返回任意数目的行。
可以返回从 1 开始递增的值，这种能力为其他许多问题的解决方案打开了方便之门。例如，我们可以生成数字，并加上日期值，这样就能生成连续的日期了。也可以借助这些数字来解析字符串。
DB2 和 SQL Server
使用 WITH 递归查询生成一系列含有递增值的行。先借助一个像 T1 这样的只有 1 行数据的表来启动行数据生成操作，其余的交给 WITH 子句即可。

 1  with x (id)
 2  as (
 3  select 1
 4    from t1
 5   union all
 6  select id+1
 7    from x
 8   where id+1 <= 10
 9  )
10  select * from x

下面是另一个替代方案，只适用于 DB2。该方案的优点是不需要 T1 表。

1  with x (id)
2  as (
3  values (1)
4   union all
5  select id+1
6    from x
7  where id+1 <= 10
8 )
9 select * from x

Oracle
使用 CONNECT BY 递归查询（适用于 Oracle 9i 及后续版本）。如果使用的是 Oracle 9i，我们要么把 CONNECT BY 放入一个内嵌视图，要么把它放进 WITH 子句。

1  with x
2  as (
3  select level id
4    from dual
5    connect by level <= 10
6  )
7  select * from x

对于 Oracle Database 10g 及后续版本，则可以用 MODEL 子句生成行数据。

1 select array id
2   from dual
3  model
4    dimension by (0 idx)
5    measures(1 array)
6    rules iterate (10) (
7      array[iteration_number] = iteration_number+1
8    )

PostgreSQL
使用 GENERATE_SERIES 函数，该函数就是为快速生成行数据而设计的。

1 select id
2   from generate_series (1,10) x(id)

SQL 生成连续的数值 扩展知识

DB2 和 SQL Server
WITH 递归查询逐步递增 ID（初始值为 1），直到背离了 WHERE 子句的条件为止。为了开启递归操作，需要先生成第一行数据，这一行里包含的值应该是 1。我们可以通过 SELECT 1 FROM T1 实现这一点，T1 表只包含一行数据；对于 DB2 而言，还可以使用 VALUES 子句生成只含有一行数据的结果集。
Oracle
本解决方案把 CONNECT BY 子查询放进了 WITH 子句。在 WHERE 子句中断之前，行数据会被连续生成出来。Oracle 会自动递增伪列 LEVEL 的值，我们不必再做什么。
在 MODEL 子句解决方案里，有一个显式的 ITERATE 命令，该命令帮助生成多行数据。如果没有 ITERATE 子句，则只返回一行数据，因为 DUAL 表仅包含一行数据。

select array id
  from dual
 model
   dimension by (0 idx)
   measures(1 array)
   rules ()
　
ID
--
 1

MODEL 子句不仅能让我们像访问数组一样访问行数据，还允许我们方便地创建新的行或返回表里不存在的行。在本解决方案中，IDX 是数组下标（数组里某个特定值的位置），ARRAY（别名 ID）是行数据构成的“数组”。第一行的默认值是 1，可以通过 ARRAY[0] 来访问。Oracle 提供了 ITERATION_NUMBER 函数，以便我们知道迭代次数。本解决方案迭代了 10 次，因而 ITERATION_NUMBER 从 0 增加到了 9。为每个 ITERATION_NUMBER 值加上 1，结果就是从 1 到 10。
执行以下查询，会更便于我们理解 MODEL 子句的作用。

select 'array['||idx||'] = '||array as output
  from dual
 model
   dimension by (0 idx)
   measures(1 array)
   rules iterate (10) (
     array[iteration_number] = iteration_number+1
   )
　
OUTPUT
------------------
array[0] = 1
array[1] = 2
array[2] = 3
array[3] = 4
array[4] = 5
array[5] = 6
array[6] = 7
array[7] = 8
array[8] = 9
array[9] = 10

PostgreSQL
全部工作都交给 GENERATE_SERIES 函数来完成。该函数有 3 个参数，它们都是数值类型。第一个参数是初始值，第二个参数是结束值，第三个参数是可选项，代表“步长”（每次增加的值）。如果没有指定第 3 个参数，则默认每次增加 1。
GENERATE_SERIES 函数功能强大，我们传递给它的参数甚至可以不是常量。例如，我们希望返回 5 行数据，初始值为 10，结束值为 30，步长为 5，那么就应该会得到如下所示的结果集。

 ID
---
 10
 15
 20
 25
 30

为了达到上述目的，我们甚至可以写出类似这样的代码。

select id
  from generate_series(
         (select min(deptno) from emp),
         (select max(deptno) from emp),
         5
       ) x(id)

需要注意的是，我们在编写以上查询代码的时候并不知道要传递给 GENERATE_SERIES 函数的参数值是什么。只有当实际执行主查询的时候，那两个子查询才会把实际的参数值计算出来。