SQL 生成从 1 到 100 的数字列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2847226/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to generate a list of numbers from 1 to 100
提问by ramachandrareddy
Using the DUAL table, how can I get a list of numbers from 1 to 100?
使用 DUAL 表，如何获得从 1 到 100 的数字列表？
回答by Peter Lang
Your question is difficult to understand, but if you want to select the numbers from 1to 100, then this should do the trick:
您的问题很难理解，但是如果您想从1to 中选择数字100，那么这应该可以解决问题：
Select Rownum r
From dual
Connect By Rownum <= 100
回答by UltraCommit
Another interesting solution in ORACLE PL/SQL:
ORACLE PL/SQL 中另一个有趣的解决方案：
SELECT LEVEL n
FROM DUAL
CONNECT BY LEVEL <= 100;
回答by Lukas Eder
Do it the hard way. Use the awesome MODELclause:
以艰难的方式去做。使用 awesomeMODEL子句：
SELECT V
FROM DUAL
MODEL DIMENSION BY (0 R)
MEASURES (0 V)
RULES ITERATE (100) (
V[ITERATION_NUMBER] = ITERATION_NUMBER + 1
)
ORDER BY 1
Proof: http://sqlfiddle.com/#!4/d41d8/20837
证明：http: //sqlfiddle.com/#!4/d41d8/ 20837
回答by echo
Using Oracle's sub query factory clause: "WITH", you can select numbers from 1 to 100:
使用 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;
回答by Unreason
Peter's answer is my favourite, too.
彼得的回答也是我最喜欢的。
If you are looking for more details there is a quite good overview, IMO, here.
Especially interesting is to read the benchmarks.
回答by vadipp
If you want your integers to be bound between two integers (i.e. start with something other than 1), you can use something like this:
如果您希望将整数绑定在两个整数之间（即以 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);
It gives:
它给：
4
5
6
7
8
回答by Lukasz Szozda
You could use XMLTABLE:
你可以使用XMLTABLE：
SELECT rownum
FROM XMLTABLE('1 to 100');
回答by Lukasz Szozda
Using GROUP BY CUBE:
使用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;
回答by Joaquinglezsantos
I created an Oracle function that returns a table of numbers
我创建了一个返回数字表的 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;
/
Is necessary create a new data type:
有必要创建一个新的数据类型：
CREATE OR REPLACE TYPE [schema]."TBL_NUMBERS" IS TABLE OF NUMBER;
/
Usage:
用法：
SELECT COLUMN_VALUE NUM FROM TABLE([schema].FN_TABLE_NUMBERS(1,10))--integers difference: 1;2;.......;10
And if you need decimals between numbers by exponencial notation:
如果您需要通过指数符号表示数字之间的小数：
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
回答by Jefferey Cave
A variant of Peter's example, that demonstrates a way this could be used to generate all numbers between 0 and 99.
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
;
Something like this becomes useful when you are doing batch identifier assignment, and looking for the items that have not yet been assigned.
当您进行批次标识符分配并查找尚未分配的项目时，这样的事情会变得很有用。
For example, if you are selling bingo tickets, you may want to assign batches of 100 floor staff (guess how i used to fund raise for sports). As they sell a batch, they are given the next batch in sequence. However, people purchasing the tickets can select to purchase any tickets from the batch. The question may be asked, "what tickets have been sold".
例如，如果您销售宾果游戏门票，您可能需要分批分配 100 名楼层员工（猜猜我过去是如何为体育运动筹集资金的）。当他们销售一批时，他们会按顺序获得下一批。但是，购买门票的人可以选择购买该批次中的任何门票。问题可能会被问到，“售出了哪些票”。
In this case, we only have a partial, random, list of tickets that were returned within the given batch, and require a complete list of all possibilities to determine which we don't have.
在这种情况下，我们只有在给定批次内返回的部分随机票证列表，并且需要所有可能性的完整列表来确定我们没有的票证。
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)
;
Excuse the use of key words, I changed some variable names from a real world example.
请原谅我使用了关键词，我从一个真实世界的例子中更改了一些变量名称。
... To demonstrate why something like this would be useful
... 证明为什么这样的东西会有用
