ORACLE SQL：获取两个数字之间的所有整数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1453747/
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
ORACLE SQL:Get all integers between two numbers
提问by Tony Andrews
Is there any way to select the numbers (integers) that are included between two numbers with SQL in Oracle; I don't want to create PL/SQL procedure or function.
有没有什么办法可以在Oracle中用SQL选择两个数字之间包含的数字（整数）；我不想创建 PL/SQL 过程或函数。
For example I need to get the numbers between 3 and 10. The result will be the values 3,4,5,6,7,8,9,10.
例如，我需要获取 3 到 10 之间的数字。结果将是值 3、4、5、6、7、8、9、10。
Thx.
谢谢。
回答by Tony Andrews
This trick with Oracle's DUAL table also works:
Oracle 的 DUAL 表的这个技巧也有效：
SQL> select n from
2 ( select rownum n from dual connect by level <= 10)
3 where n >= 3;
N
----------
3
4
5
6
7
8
9
10
回答by paxdiablo
The firstthing I do when I create a new database is to create and populate some basic tables.
创建新数据库时，我做的第一件事是创建并填充一些基本表。
One is a list of all integers between -N and N, another is a list of dates 5 years in the past through 10 years in the future (a scheduled job can continue creating these as needed, going forward) and the last is a list of all hours throughout the day. For example, the inetgers:
一个是 -N 和 N 之间所有整数的列表，另一个是过去 5 年到未来 10 年的日期列表（预定的作业可以根据需要继续创建这些，向前推进），最后一个是列表全天所有时间。例如， inetgers：
create table numbers (n integer primary key);
insert into numbers values (0);
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
insert into numbers select n+128 from numbers; commit;
insert into numbers select n+256 from numbers; commit;
insert into numbers select n+512 from numbers; commit;
insert into numbers select n+1024 from numbers; commit;
insert into numbers select n+2048 from numbers; commit;
insert into numbers select n+4096 from numbers; commit;
insert into numbers select n+8192 from numbers; commit;
insert into numbers select -n from numbers where n > 0; commit;
This is for DB2/z which has automatic transaction start which is why it seems to have naked commits.
这适用于具有自动事务启动的 DB2/z，这就是为什么它似乎具有裸提交。
Yes, it takes up a (minimal) space but it makes queries mucheasier to write, simply by selecting values from those tables. It's also very portable across pretty much anySQL-based DBMS.
是的，它占用（最小）空间，但它使查询更容易编写，只需从这些表中选择值即可。它在几乎所有基于 SQL 的 DBMS 中也非常具有可移植性。
Your particular query would then be a simple:
您的特定查询将是一个简单的：
select n from numbers where n >=3 and n <= 10;
The hour figures and date ranges are quite useful for the sort of reporting applications we work on. It allows us to create zero entries for those hours of the day (or dates) which don't have any real data so that, instead of (where there's no data on the second of the month):
小时数字和日期范围对于我们处理的报告应用程序非常有用。它允许我们为没有任何真实数据的一天中的那些小时（或日期）创建零条目，而不是（当月的第二个没有数据的地方）：
Date | Quantity
-----------+---------
2009-01-01 | 7
2009-01-03 | 27
2009-01-04 | 6
we can instead get:
我们可以改为：
Date | Quantity
-----------+---------
2009-01-01 | 7
2009-01-02 | 0
2009-01-03 | 27
2009-01-04 | 6
回答by Rob van Wijk
SQL> var N_BEGIN number
SQL> var N_END number
SQL> exec :N_BEGIN := 3; :N_END := 10
PL/SQL procedure successfully completed.
SQL> select :N_BEGIN + level - 1 n
2 from dual
3 connect by level <= :N_END - :N_BEGIN + 1
4 /
N
----------
3
4
5
6
7
8
9
10
8 rows selected.
This uses the same trick as Tony's. Note that when you are using SQL*Plus 9, you have to make this query an inline view as Tony showed you. In SQL*Plus 10 or higher, the above is sufficient.
这使用与托尼相同的技巧。请注意，当您使用 SQL*Plus 9 时，您必须使该查询成为内联视图，正如 Tony 向您展示的那样。在 SQL*Plus 10 或更高版本中，上述内容就足够了。
Regards, Rob.
问候，罗布。
回答by Gary Myers
You can use the MODEL clause for this.
您可以为此使用 MODEL 子句。
SELECT c1 from dual
MODEL DIMENSION BY (1 as rn) MEASURES (1 as c1)
RULES ITERATE (7)
(c1[ITERATION_NUMBER]=ITERATION_NUMBER+7)
回答by Thiyagu ATR
this single line query will help you,
这个单行查询会帮助你，
select level lvl from dual where level<:upperbound and
level >:lowerbound connect by level<:limt
For your case:
对于您的情况：
select level lvl from dual where level<10 and level >3 connect by level<11
let me know if any clarification.
如果有任何澄清，请告诉我。
回答by Codo
This is a late addition. But the solution seems to be more elegant and easier to use.
这是一个迟到的补充。但该解决方案似乎更优雅，更易于使用。
It uses a pipelined function that has to be installed once:
它使用一个必须安装一次的流水线函数：
CREATE TYPE number_row_type AS OBJECT
(
num NUMBER
);
CREATE TYPE number_set_type AS TABLE OF number_row_type;
CREATE OR REPLACE FUNCTION number_range(p_start IN PLS_INTEGER, p_end IN PLS_INTEGER)
RETURN number_set_type
PIPELINED
IS
out_rec number_row_type := number_row_type(NULL);
BEGIN
FOR i IN p_start .. p_end LOOP
out_rec.num := i;
pipe row(out_rec);
END LOOP;
END number_range;
/
Then you can use it like this:
然后你可以像这样使用它：
select * from table(number_range(1, 10));
NUM
---
1
2
3
4
5
6
7
8
9
10
The solution is Oracle specific.
解决方案是特定于 Oracle 的。
回答by Lukasz Szozda
One way to generate numbers from range is to use XMLTABLE('start to end'):
从范围生成数字的一种方法是使用XMLTABLE('start to end')：
SELECT column_value
FROM XMLTABLE('3 to 10');
回答by anthian
Gary, to show the result that he explained, the model query will be:
Gary，为了显示他解释的结果，模型查询将是：
SELECT c1
FROM DUAL
MODEL DIMENSION BY (1 as rn)
MEASURES (1 as c1)
RULES ITERATE (8)
(c1[ITERATION_NUMBER]=ITERATION_NUMBER+3)
ORDER BY rn
SELECT c1 from DUAL MODEL DIMENSION BY (1 as rn)
MEASURES (1 as c1) 规则 ITERATE (8) (c1[ITERATION_NUMBER]=ITERATION_NUMBER+3) ORDER BY rn
;)
;)
I always use:
我总是使用：
SELECT (LEVEL - 1) + 3 as result FROM Dual CONNECT BY Level <= 8
SELECT (LEVEL - 1) + 3 as result FROM Dual CONNECT BY Level <= 8
Where 3 is the start number and 8 is the number of "iterations".
其中 3 是起始编号，8 是“迭代次数”。
回答by Amit
Or you can use Between
或者你可以使用之间
Select Column1 from dummy_table where Column2 Between 3 and 10
回答by Gaspa79
I just did a table valued function to do this in SQL server, if anyone is interested, this works flawlessly.
我只是做了一个表值函数来在 SQL 服务器中执行此操作，如果有人感兴趣，它可以完美运行。
CREATE FUNCTION [dbo].[NumbersBetween]
(
@StartN int,
@EndN int
)
RETURNS
@NumberList table
(
Number int
)
AS
BEGIN
WHILE @StartN <= @EndN
BEGIN
insert into @NumberList
VALUES (@StartN)
set @StartN = @StartN + 1
END
Return
END
GO
If you run the query: "select * from dbo.NumbersBetween(1,5)" (w/o the quotes of course) the result will be
如果您运行查询：“select * from dbo.NumbersBetween(1,5)”（当然没有引号）结果将是
Number
-------
1
2
3
4
5
