SQL SQL查询从双重返回N行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1973676/
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 Query to return N rows from dual
提问by Harish
I want to write a SQL query which accepts a bind variable (say :NUM) and its output consists of one column & :NUM number of rows, each row having its row number. i.e. if we pass :NUM as 7, the output should be:
我想编写一个 SQL 查询，它接受一个绑定变量（比如：NUM），它的输出由一列和：NUM 行组成，每一行都有它的行号。即，如果我们将 :NUM 作为 7 传递，则输出应为：
VAL
====
1
2
3
4
5
6
7
There shouldn't be any actual DB tables in query and no PL/SQL code should be used. i.e. only dual should be used in the query
查询中不应有任何实际的数据库表，也不应使用任何 PL/SQL 代码。即只应在查询中使用双
Is there any way to achieve this?
有没有办法实现这一目标？
回答by OMG Ponies
You could use:
你可以使用：
WHERE ROWNUM <= :NUM
...but the table has to contain row equal or greater to the limit in the bind variable. This link demonstrates various row number generation techniques in Oracle.
...但该表必须包含等于或大于绑定变量中的限制的行。 此链接演示了 Oracle 中的各种行号生成技术。
Using CONNECT BY, Oracle 10g+:
使用CONNECT BY，Oracle 10g+：
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= :NUM
Confirmed by monojohnnythat the bind variable can be used. Attempts to run on Oracle 9i, though CONNECT BYsyntax is supported results in an ORA-01436 error.
确认monojohnny可以使用绑定变量。尝试在 Oracle 9i 上运行，但CONNECT BY支持语法会导致 ORA-01436 错误。
The only thing I'm not 100% on is if the CONNECT BY will accept the limit from the bind variable.
我唯一不是 100% 的问题是 CONNECT BY 是否会接受来自绑定变量的限制。
Reference:
参考：
回答by Rob Farley
Try something like:
尝试类似：
SELECT 1 AS Val FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
UNION ALL SELECT 4 FROM dual
UNION ALL SELECT 5 FROM dual
UNION ALL SELECT 6 FROM dual
UNION ALL SELECT 7 FROM dual;
It's messy, but it'll do the trick.
这很乱，但它会解决问题。
Edited: Ah - you need to pass in a variable to let you know how high to go...
编辑：啊 - 你需要传入一个变量来让你知道要走多高......
So how about something like:
那么像这样的事情怎么样：
SELECT t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 AS Val
FROM
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t1,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t2,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t3,
(
SELECT 0 AS Val FROM dual
UNION ALL SELECT 1 FROM dual
) AS t4
WHERE t1.Val + t2.Val * 2 + t3.Val * 4 + t4.Val * 8 <= 7;
Ok... editing again, now using WITH:
好的...再次编辑，现在使用 WITH：
WiTH
A0 AS (SELECT 0 as N FROM DUAL UNION ALL SELECT 0 FROM DUAL),
A1 AS (SELECT 0 as N FROM A0, A0 AS B),
A2 AS (SELECT 0 as N FROM A1, A1 AS B),
A3 AS (SELECT 0 as N FROM A2, A2 AS B),
A4 AS (SELECT 0 as N FROM A3, A3 AS B),
A5 AS (SELECT 0 as N FROM A4, A4 AS B),
A6 AS (SELECT 0 as N FROM A5, A5 AS B),
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY N) AS Val FROM A6)
SELECT *
FROM Nums
WHERE Val <= :NUM
;
回答by monojohnny
I didn't come up with this answer [ so make sure any votes go the right way!!] , it just my testing notes based on 'OMG Ponies' [who wasn't sure whether the method would work with binding variable] above for reference:
我没有想出这个答案[所以请确保任何投票都以正确的方式进行！！]，这只是我基于“OMG Ponies”的测试笔记[谁不确定该方法是否适用于绑定变量] 上面以供参考：
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> var num_rows number
SQL> begin select 20 into :num_rows from dual;
2 end;
3 /
PL/SQL procedure successfully completed.
SQL> select level from dual
2 connect by level <=:num_rows;
LEVEL
----------
1
2
3
4
...
回答by lunicon
Query without connect by
查询无连接方式
WITH num(n) as(select 1 from dual union all
select n+1 from num where n <= :num_limit)
select * from num
回答by Jeffrey Kemp
Another way is to use an XQuery range expression, e.g.:
另一种方法是使用 XQuery 范围表达式，例如：
select column_value from xmltable(:a||' to '||:b);
1
2
3
4
5
6
7
8
9
10
This solution is quite flexible, e.g.:
这个解决方案非常灵活，例如：
select column_value from xmltable('5 to 10, 15 to 20');
5
6
7
8
9
10
15
16
17
18
19
20
回答by Parshuram Kalvikatte
WITH cte_numbers(n)
AS (
SELECT 0
UNION ALL
SELECT n + 1
FROM cte_numbers
WHERE n < 10
)
SELECT n
FROM cte_numbers;
Returned Rows 0 1 2 3 4 5 6 7 8 9 10
返回的行 0 1 2 3 4 5 6 7 8 9 10
回答by R. Genaro
Another solution would require some PL/SQL to create a function to return a collection with the rows... Not as simple as the select level from dual connect by level <= :b1approach, but it's useful in a few situations:
另一种解决方案需要一些 PL/SQL 来创建一个函数来返回一个包含行的集合......不像方法那么简单select level from dual connect by level <= :b1，但它在某些情况下很有用：
1) Create a number table object type ( number_tbl, in this example ) :
1）创建一个数字表对象类型（number_tbl，在这个例子中）：
create or replace type number_tbl as table of number;
2) Create a function that will receive the number of rows to be generated, and then return a number_tbl object with the results:
2）创建一个接收要生成的行数的函数，然后返回一个带有结果的number_tbl对象：
create or replace function get_rows( i_num_rows number ) return number_tbl as
t number_tbl := number_tbl();
begin
if i_num_rows < 1 then
return null;
end if;
t.extend( i_num_rows );
for i in 1..i_num_rows loop
t(i) := i;
end loop;
return t;
end get_rows;
3) select from your function using the table( ... )function to turn your number_tbl object into something selectable:
3）使用该table( ... )函数从您的函数中选择将您的 number_tbl 对象转换为可选择的对象：
select * from table( cast ( get_rows( :b1 ) as number_tbl ) );
回答by Sushant Butta
connect by is such a wonderful thing. It helps you generated multiple rows with a single set of data available in dual table. This can help you generate huge no of rows for your dummy data. For example
connect by 是一件多么美妙的事情。它可以帮助您使用双表中可用的一组数据生成多行。这可以帮助您为虚拟数据生成大量行。例如
insert into test select a.* from test1 a,(select * from dual connect by level <=100000) b;
or you can do something like this
或者你可以做这样的事情
Example 2 : You want to print square and cube of numbers from 1 to 10.
示例 2：您想打印从 1 到 10 的数字的平方和立方。
SQL> select level "No", power(level,2) "Square", power(level,3) "Cube" from dual connect by level <= 10;
No Square Cube
---------- ---------- ----------
1 1 1
2 4 8
3 9 27
4 16 64
5 25 125
6 36 216
7 49 343
8 64 512
9 81 729
10 100 1000
Hence you can manipulate it in whatever form you want. This is how you can return multiple rows from dual table. References : http://www.oraclebin.com/2012/12/multipe-rows-from-dual-table.html
因此，您可以以任何您想要的形式操作它。这就是如何从双表返回多行。参考资料：http: //www.oraclebin.com/2012/12/multipe-rows-from-dual-table.html
回答by paxdiablo
I'm marking this community wiki since it doesn't actually answer your requirement for no tables, but one of the first things we do when installing a database is to create a set of tables for this sort of purpose.
我正在标记这个社区 wiki，因为它实际上并没有满足您对无表的要求，但是我们在安装数据库时做的第一件事就是为此目的创建一组表。
- A table containing a large number of integers (e.g., -99999 through 99999).
- A table containing every date from 10 years in the past to 10 years in the future (which is continuously added to each month and trimmed occasionally).
- A table containing each hour of the day.
- 包含大量整数（例如，-99999 到 99999）的表。
- 包含从过去 10 年到未来 10 年的每个日期的表格（不断添加到每个月并偶尔修剪）。
- 包含一天中每个小时的表。
By doing this, we greatly reduce the complexity, and increase the speed, of a large number of our queries at the cost of (minimal and cheap) disk space.
通过这样做，我们以（最少且便宜的）磁盘空间为代价大大降低了大量查询的复杂性并提高了速度。
You should give some serious thought to that. Aside from maintaining the date table, there's not a lot of upkeep needed.
你应该认真考虑一下。除了维护日期表外，不需要太多维护。
回答by Grzegorz Gierlik
Depends on database various method can be used.
取决于数据库，可以使用各种方法。
PostgreSQLhas a nice feature -- series.
PostgreSQL有一个很好的特性——系列。
To get what you want just want:
为了得到你想要的只是想要的：
SELECT * FROM generate_series(1, NUM);
