postgresql SQL：从“无”中选择一个数字列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17673549/
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: Select a list of numbers from "nothing"
提问by Juve
What is a fast/readable way to SELECT a relation from "nothing" that contains a list of numbers. I want to define which numbers by setting a start and end value. I am using Postgres SQL and SQLite, and would be interested in genericsolutions that will work on both/many platforms.
从包含数字列表的“无”中选择关系的快速/可读方法是什么。我想通过设置开始和结束值来定义哪些数字。我正在使用 Postgres SQL 和 SQLite，并且对适用于两个/许多平台的通用解决方案感兴趣。
Desired output relation:
期望的输出关系：
# x
0
1
2
3
4
I know that I can SELECT a single row from "nothing": SELECT 0,1,2,3,4But this selects the values as columns instead of rows and requires to specify all values in the query instead of only using my start and end values: 0and 4.
我知道我可以从“nothing”中SELECT 0,1,2,3,4选择一行：但这会将值选择为列而不是行，并且需要在查询中指定所有值，而不是仅使用我的开始和结束值：0和4。
In Postgres you have a special generate_seriesfunction for this case:
在 Postgres 中，generate_series对于这种情况，您有一个特殊的功能：
SELECT * FROM generate_series(0,4) x;
This works nicely but is non-standard. I can also imagine some complicated solutions using temporary tables, but I would like to have something generic AND simple like:
这很好用，但不是标准的。我也可以想象一些使用临时表的复杂解决方案，但我想要一些通用且简单的东西，例如：
SELECT * FROM [0..4]
Maybe using the SEQUENCEstatement or some magic combination of SELECT 0and SELECT 4?
也许使用SEQUENCE陈述或一些神奇的组合SELECT 0和SELECT 4？
采纳答案by Juve
Thanks for all answers! Following the discussion I realized that using a numbers tableis not too complicated and works well and fast on both/many platforms:
感谢所有的答案！在讨论之后，我意识到使用数字表并不太复杂，并且在两个/许多平台上都能很好地运行：
CREATE TABLE integers (i integer);
INSERT INTO integers (i) VALUES (0);
INSERT INTO integers (i) VALUES (1);
...
INSERT INTO integers (i) VALUES (9);
SELECT (hundreds.i * 100) + (tens.i * 10) + units.i AS x
FROM integers AS units
CROSS JOIN integers AS tens
CROSS JOIN integers AS hundreds
You just create this table once and can use it whenever you need a range of numbers.
您只需创建此表一次，就可以在需要一系列数字时使用它。
回答by Roman Pekar
Well in SQL server (and PostgreSQL) I would use recursive common table expression: SQL Server, PostgreSQL
在 SQL Server（和 PostgreSQL）中，我会使用递归公用表表达式：SQL Server, PostgreSQL
with recursive Numbers as (
select 0 as Number
union all
select Number + 1
from Numbers
where Number < 4
)
select Number
from Numbers
But, as far as I know, there's no WITH in SQLite.
但是，据我所知，SQLite 中没有 WITH。
So, the possible solutions could be
因此，可能的解决方案是
- create a user defined function (thiscould be helpful)
create a table with numbers from 0 to max number you'll ever need, and then just select from it like this:
select Number from Numbers where Number >= 0 and Number <= 4
- 创建一个用户定义的函数（这可能会有所帮助）
创建一个从 0 到您需要的最大数字的表格，然后像这样从中选择：
select Number from Numbers where Number >= 0 and Number <= 4
回答by gphil
A simple way to do this in PostgreSQL and SQLite is as follows:
在 PostgreSQL 和 SQLite 中执行此操作的简单方法如下：
sqlite> select 1 union select 2 union select 3;
1
2
3
It should work in most RDBMS systems, but IIRC in Oracle you would have to use:
它应该适用于大多数 RDBMS 系统，但在 Oracle 中您必须使用 IIRC：
select 1 from dual union select 2 from dual union select 3 from dual;
But I don't have an Oracle DB to test it on.
但我没有一个 Oracle DB 来测试它。
回答by mdev
Some other alternative (tried in postgres, but should be working with others)
其他一些替代方案（在 postgres 中尝试过，但应该与其他人合作）
select (0) as count union values (1),(2),(3),(4),(5)
select (0) as count union values (1),(2),(3),(4),(5)
Extending this sql with code will be straight forward.
用代码扩展这个 sql 将是直截了当的。
