SQL：从“无”中选择数字列表。 [英] SQL: Select a list of numbers from "nothing"

问题描述

从包含数字列表的无中选择关系的快速/可读方法是什么。我想通过设置开始和结束值来定义哪些数字。我正在使用Postgres SQL和SQLite，并且对在两个平台上均可使用的通用解决方案感兴趣。

所需的输出关系：

 ＃x 
 0 
 1 
 2 
 3 
 4 
  

我知道我可以从无中选择一行： SELECT 0,1,2,3,4 但这将值选择为列而不是行，并且需要在查询中指定所有值，而不是仅使用我的开始和结束值： 0 和 4 。

在Postgres中，您有一个特殊的 generate_series 在这种情况下的功能：

  SELECT * FROM generate_series（0,4）x ; 
  

这很好用，但不是标准的。我也可以想象使用临时表的一些复杂解决方案，但是我想拥有一些通用而又简单的东西：

  SELECT * FROM [0..4] 
  

也许使用 SEQUENCE 语句或 SELECT 0 和 SELECT 4 的某种魔术组合？

解决方案

感谢所有答案！
讨论之后，我意识到使用数字表不太合适

  CREATE TABLE整数（i整数）；并且在两个/多个平台上都运行良好且快速。 
插入整数（i）值（0）; 
插入整数（i）值（1）; 
 ... 
插入整数（i）值（9）; 
 SELECT（几百.i * 100）+（tens.i * 10）+ units.i AS x 
 FROM整数AS单位
交叉联接整数AS十
交叉联接整数AS数百美元
  

您只需创建一次此表，便可以在需要一系列数字时使用它。 / p>

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 generic solutions that will work on both/many platforms.

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,4 But 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: 0 and 4.

In Postgres you have a special generate_series function for this case:

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 SEQUENCE statement or some magic combination of SELECT 0 and SELECT 4?

解决方案

Thanks for all answers! Following the discussion I realized that using a numbers table is 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.

这篇关于SQL：从“无”中选择数字列表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！