SQL (Oracle) Select 没有包含静态数据的实际表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9464942/
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 (Oracle) Select without an actual table containing static data
提问by Sauer
I am looking for a possibility to run an (Oracle) SQL without querying an actual table. Here I found a tip with the DUAL table. Thats pretty good. But I'm looking for a short solution to select MULTIPLE rows from "nowhere". Is that possible? Here is the shortest query I can think of, which does what I need:
我正在寻找一种在不查询实际表的情况下运行（Oracle）SQL 的可能性。在这里，我发现了一个关于 DUAL 表的提示。那很好。但我正在寻找一个简短的解决方案来从“无处”中选择多行。那可能吗？这是我能想到的最短查询，它满足我的需求：
Select 1, 'foo' FROM DUAL union
Select 2, 'bar' FROM DUAL union
Select 3, 'more stuff' FROM DUAL
But if I want to have more rows in my result, it gets pretty unhandy. Is there a shorter way? Something like
但是如果我想在我的结果中有更多的行，它会变得非常不方便。有没有更短的方法？就像是
Select 1, 'foo'; 2, 'bar'; 3, 'more stuff' from dual
or
Select * from (1, 'foo') union (2, 'bar') union (3, 'more stuff')
I know, that doesn't work, but do you have an idea? Is there an easy way to transpose a queries' result? Something like:
我知道，那行不通，但你有什么想法吗？有没有一种简单的方法来转置查询的结果？就像是：
Select transposed (1, 'foo', 2, 'bar', 3, 'more stuff') from dual
I want to keep the amount "overhead-characters" at the lowest.
我想将“开销字符”的数量保持在最低水平。
回答by
You can use connect by levelto generate more records - something like:
您可以使用connect by level生成更多记录 - 例如：
select level, decode(level, 1,'foo', 2,'bar', 3,'more stuff')
from dual connect by level <= 3
回答by user 923227
In case there are multiple Columns - then this works as well -
如果有多个列 - 那么这也适用 -
select
REGEXP_SUBSTR (jango,'^[^#]*') as f1,
REGEXP_SUBSTR(REGEXP_REPLACE(jango,'^([^#]*#){1}', ''),'^[^#]*') as f2,
REGEXP_REPLACE(jango,'^([^#]*#){2}', '') as f3
from
(
Select decode(level,
1, 'foo#koo#joo',
2, 'bar#loo#too' ,
3, 'more stuff#doo#dingo') as jango
from dual connect by level <= 20
)
Here is how it works - The inner query is same as above I have appended multiple Columns using # - need to take care that it is not a part in the regex family else we need to escape it.
这是它的工作原理 - 内部查询与上面相同，我使用 # 附加了多个列 - 需要注意它不是正则表达式系列的一部分，否则我们需要对其进行转义。
Select decode(level,
1, 'foo#koo#joo',
2, 'bar#loo#too' ,
3, 'more stuff#doo#dingo') as jango
from dual connect by level <= 20
Gives the following -
给出以下 -
Jango
-------------------
foo#koo#joo
bar#loo#too
more stuff#doo#dingo
Now the following piece selects from the output column - 'jango', anything upto #
现在下面的部分从输出列中选择 - 'jango'，任何高达 #
REGEXP_SUBSTR (jango,'^[^#]*') as f1,
O/p --> foo
for the Second column we remove the contents of the 1st column followed by #
对于第二列，我们删除第一列的内容，后跟 #
REGEXP_REPLACE(jango,'^([^#]*#){1}', '')
we get --> koo#joo
Now the 1st step - get the first field.
现在第一步 - 获得第一个字段。
for more fields {1} can be increased.
对于更多字段，可以增加 {1}。
