Oracle 从具有多行和多列的 Dual 中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28202851/
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 select from dual with multiple rows and columns
提问by Jeremy
I need to join against a dynamic list of numbers that I retrieve from my program dynamically. The number of rows is not fixed, nor are the numbers that are used.
我需要加入从我的程序中动态检索的动态数字列表。行数不是固定的，使用的数字也不是固定的。
I am not finding a better way to accomplish this than with the following (for my purposes, a temporary table is not helpful):
我没有找到比以下更好的方法来实现这一点（就我而言，临时表没有帮助）：
select 111 as col1, 322 as col2 from dual
union all
select 3 as col1, 14 as col2 from dual
union all
select 56 as col1, 676 as col2 from dual;
Is there a better way to do this? I see that there is a connect by statement that can return multiple rows, but I'm not seeing a way to do multiple rows and columns.
有一个更好的方法吗？我看到有一个 connect by 语句可以返回多行，但我没有看到执行多行和多列的方法。
回答by B. Khan
You can use the decode and connect by level:
您可以使用解码并按级别连接：
select decode(rownum, 1, 111, 2, 3, 3, 56) as col1,
decode(rownum, 1, 322, 2, 14, 3, 676) as col2
from dual
connect by level <= 3;
回答by Ed Gibbs
You can use the CONNECT BYhere with a little math:
你可以使用CONNECT BY这里的一点数学：
SELECT Level * 2 - 1 AS col1, Level * 2 AS col2
FROM DUAL
CONNECT BY LEVEL <= 3;
That will give you your example of three rows. Adjust the LEVEL <= ...value to get more rows.
这将为您提供三行的示例。调整LEVEL <= ...值以获得更多行。
