SQL生成从1到100的数字列表

Question

使用 DUAL 表，如何获得从 1 到 100 的数字列表？

Answer 1

Peter 示例的一个变体，演示了一种可用于生成 0 到 99 之间的所有数字的方法。

with digits as (
  select mod(rownum,10) as num 
  from   dual 
  connect by rownum <= 10
)
select a.num*10+b.num as num 
from   digits a
       ,digits b
order by num
;

当您进行批量标识符分配并查找尚未分配的项目时，类似的方法会变得很有用。

例如，如果您销售宾果游戏门票，您可能需要分批分配 100 名场内工作人员（猜猜我以前是如何为体育赛事筹集资金的）。当他们销售一批产品时，他们会按顺序获得下一批产品。但是，购买门票的人可以选择购买该批次中的任何门票。可能会问“已经售出哪些票”的问题。

在这种情况下，我们只有给定批次内返回的部分随机票证列表，并且需要所有可能性的完整列表来确定我们没有哪些票证。

with range as (
  select mod(rownum,100) as num 
  from   dual 
  connect by rownum <= 100
),
AllPossible as (
  select a.num*100+b.num as TicketNum
  from   batches a
         ,range b
  order by num
)
select TicketNum as TicketsSold
from   AllPossible
where  AllPossible.Ticket not in (select TicketNum from TicketsReturned)
;

请原谅使用关键字，我更改了现实世界示例中的一些变量名称。

...为了证明为什么这样的东西会有用

Answer 2

我创建了一个返回数字表的 Oracle 函数

CREATE OR REPLACE FUNCTION [schema].FN_TABLE_NUMBERS(
    NUMINI INTEGER,
    NUMFIN INTEGER,
    EXPONENCIAL INTEGER DEFAULT 0
) RETURN TBL_NUMBERS
IS
    NUMEROS TBL_NUMBERS;
    INDICE NUMBER;
BEGIN
    NUMEROS := TBL_NUMBERS();

    FOR I IN (
        WITH TABLA AS (SELECT NUMINI, NUMFIN FROM DUAL)
        SELECT NUMINI NUM FROM TABLA UNION ALL
        SELECT 
            (SELECT NUMINI FROM TABLA) + (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) NUM
        FROM DUAL
        CONNECT BY 
            (LEVEL*TO_NUMBER('1E'||TO_CHAR(EXPONENCIAL))) <= (SELECT NUMFIN-NUMINI FROM TABLA)
    ) LOOP
        NUMEROS.EXTEND;
        INDICE := NUMEROS.COUNT; 
        NUMEROS(INDICE):= i.NUM;
    END LOOP;

    RETURN NUMEROS;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
       RETURN NUMEROS;
  WHEN OTHERS THEN
       RETURN NUMEROS;
END;
/

有必要创建一个新的数据类型：

CREATE OR REPLACE TYPE [schema]."TBL_NUMBERS" IS TABLE OF NUMBER;
/

用法：

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10))--integers difference: 1;2;.......;10

如果您需要通过指数表示法在数字之间进行小数：

SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-1));--with 0.1 difference: 1;1.1;1.2;.......;10
SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10,-2));--with 0.01 difference: 1;1.01;1.02;.......;10

Answer 3

如果要生成数字 1 - 100 的列表，可以使用 {1,2,3,4,5,6,6,7,8,9,10} X {0,10,20, 30,40,50,60,70,80,90}
https://en.wikipedia.org/wiki/Cartesian_product
大致如下：

SELECT
    ones.num + tens.num
FROM
    (
        SELECT 1 num UNION ALL
        SELECT 2 num UNION ALL
        SELECT 3 num UNION ALL
        SELECT 4 num UNION ALL
        SELECT 5 num UNION ALL
        SELECT 6 num UNION ALL
        SELECT 7 num UNION ALL
        SELECT 8 num UNION ALL
        SELECT 9 num UNION ALL
        SELECT 10 num
    ) as ones
CROSS JOIN
 (
     SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num
 ) as tens;

我无法在 Oracle 数据库上测试这一点，您可以将双精度放在它所属的位置，但它应该可以工作。

Answer 4

WITH ones AS
    (
        SELECT 1 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 2 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 3 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 4 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 5 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 6 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 7 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 8 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 9 num   /* oracle add here FROM DUAL */ UNION ALL
        SELECT 10 num  /* oracle add here FROM DUAL */ 
    ),
tens AS
(
    SELECT 10 * (num -1) AS num FROM ones
)
SELECT
    ones.num + tens.num
FROM ones, tens  -- cross join
ORDER BY 1;

Answer 5

最简单的解决方案是使用公共表表达式递归逻辑，如下所示：

WITH cte_numbers
AS
(SELECT
        1 row_num
    UNION ALL
    SELECT
        row_num + 1
    FROM cte_numbers cn
    WHERE cn.row_num < 100)
SELECT
    row_num
FROM cte_numbers

Answer 6

SELECT * FROM `DUAL` WHERE id>0 AND id<101

上面的查询是在数据库中用SQL编写的。

Answer 7

你的问题很难理解，但如果你想选择从 1 到 100 的数字，那么这应该可以解决问题：

Select Rownum r
From dual
Connect By Rownum <= 100

Answer 8

ORACLE PL/SQL 中另一个有趣的解决方案：

    SELECT LEVEL n
      FROM DUAL
CONNECT BY LEVEL <= 100;

Answer 9

使用Oracle的子查询工厂子句：“WITH”，您可以选择从1到100的数字：

WITH t(n) AS (
  SELECT 1 from dual
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;

Answer 10

您可以使用 XMLTABLE：

SELECT rownum
FROM XMLTABLE('1 to 100');

-- alternatively(useful for generating range i.e. 10-20)
SELECT (COLUMN_VALUE).GETNUMBERVAL() AS NUM
FROM XMLTABLE('1 to 100');

DBFiddle 演示

Answer 11

用困难的方式去做吧。使用很棒的 MODEL 子句：

SELECT V
FROM DUAL
MODEL DIMENSION BY (0 R)
      MEASURES (0 V)
      RULES ITERATE (100) (
        V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
      )
ORDER BY 1

证明：http://sqlfiddle.com /#!4/d41d8/20837

Answer 12

如果您希望整数绑定在两个整数之间（即以 1 以外的数字开头），您可以使用如下所示的内容：

with bnd as (select 4 lo, 9 hi from dual)
select (select lo from bnd) - 1 + level r
from dual
connect by level <= (select hi-lo from bnd);

它给出：

4
5
6
7
8

Answer 13

彼得的回答也是我最喜欢的。

如果您正在寻找更多详细信息，IMO，此处有一个非常好的概述。
特别有趣的是阅读基准。

Answer 14

使用GROUP BY CUBE：

SELECT ROWNUM
FROM (SELECT 1 AS c FROM dual GROUP BY CUBE(1,1,1,1,1,1,1) ) sub
WHERE ROWNUM <=100;

Rextester 演示