MySQL SQL SELECT 获取前 N 个正整数

SQL SELECT to get the first N positive integers

提问by monn

I need to get a result set containing the first N positive integers. Is it possible to use only standard SQL SELECT statement to get them (without any count table provided)?

我需要得到一个包含前 N 个正整数的结果集。是否可以仅使用标准 SQL SELECT 语句来获取它们（不提供任何计数表）？

If it's not possible, is there any specific MySQL way to achieve this?

如果不可能，是否有任何特定的 MySQL 方法可以实现这一目标？

回答by Quassnoi

Seems that what you want is a dummy rowset.

似乎你想要的是一个dummy rowset.

In MySQL, it's impossible without having a table.

在MySQL，没有桌子是不可能的。

Most major systems provide a way to do it:

大多数主要系统都提供了一种方法：

• In Oracle:

  SELECT  level
  FROM    dual
  CONNECT BY
          level <= 10
  

• In SQL Server:

  WITH    q AS
          (
          SELECT  1 AS num
          UNION ALL
          SELECT  num + 1
          FROM    q
          WHERE   num < 10
          )
  SELECT  *
  FROM    q
  

• In PostgreSQL:

  SELECT  num
  FROM    generate_series(1, 10) num
  

• 在Oracle：

  SELECT  level
  FROM    dual
  CONNECT BY
          level <= 10
  

• 在SQL Server：

  WITH    q AS
          (
          SELECT  1 AS num
          UNION ALL
          SELECT  num + 1
          FROM    q
          WHERE   num < 10
          )
  SELECT  *
  FROM    q
  

• 在PostgreSQL：

  SELECT  num
  FROM    generate_series(1, 10) num
  

MySQLlacks something like this and this is a serious drawback.

MySQL缺乏这样的东西，这是一个严重的缺点。

I wrote a simple script to generate test data for the sample tables in my blog posts, maybe it will be of use:

我写了一个简单的脚本来为我的博客文章中的示例表生成测试数据，也许它会有用：

CREATE TABLE filler (
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

You call the procedure and the table gets filled with the numbers.

你调用这个过程，表格就会被数字填满。

You can reuse it during the duration of the session.

您可以在会话期间重复使用它。

回答by luigi

A possible solution (admittedly not very elegant) is to use any table with a sufficiently large number of records.

一个可能的解决方案（诚然不是很优雅）是使用具有足够多记录的任何表。

For the first 10 integers (using the mysql.help_relation, but any table would do), you could use the following query:

对于前 10 个整数（使用 mysql.help_relation，但任何表都可以），您可以使用以下查询：

SELECT  @N := @N +1 AS integers 
FROM mysql.help_relation , (SELECT @N:=0) dum LIMIT 10;

This could also be placed in a function taking Min and Max.

这也可以放在一个采用 Min 和 Max 的函数中。

回答by Vladislav Rastrusny

Weird solution, but...

奇怪的解决方案，但是...

SELECT 1 UNION SELECT 2 UNION SELECT 3....

回答by JeanValjean

The sequence I propose allows the programmer to execute the following query :

我建议的序列允许程序员执行以下查询：

select value from sequence where value>=15 and value<100;

And to obtain the expected results : the sequence of integers between 15 (inclusive) and 100 (exclusive).

并获得预期结果：15（含）和100（不含）之间的整数序列。

If that's what you want, you'll have to create the two following views, views that you'll declare only once :

如果这就是你想要的，你必须创建以下两个视图，你只声明一次的视图：

create view digits as select 0 n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9;

create view sequence as select u.n+t.n*10+h.n*100 as value from digits as u cross join digits as t cross join digits as h;

This way you have your sequence with an intuitive SELECT...

通过这种方式，您可以使用直观的 SELECT...

Hope it helps.

希望能帮助到你。

回答by Alistair Evans

Assuming you mean retrieve them from a table, here N is 10, assuming intcolumn is the column with numbers in it.

假设您的意思是从表中检索它们，这里 N 是 10，假设 intcolumn 是其中包含数字的列。

SELECT intcolumn FROM numbers WHERE intcolumn > 0 LIMIT 10

Edit: In case you were actually looking to get the mathematical set of positive numbers without a table, I would reconsider, it can be intensive (depending on the implementation). Commonly accepted practice seems to be to create a lookup table full of numbers, and then use the above query.

编辑：如果您实际上希望在没有表格的情况下获得正数的数学集，我会重新考虑，它可能很密集（取决于实现）。普遍接受的做法似乎是创建一个充满数字的查找表，然后使用上述查询。

回答by Antti Kissaniemi

If you know that Nis limited (and usually it is), you can use a construction such as:

如果您知道这N是有限的（通常是这样），您可以使用以下结构：

select (a.digit + (10 * b.digit) + (100 * c.digit) + (1000 * d.digit) + (10000 * e.digit) + (100000 * f.digit)) as n
    from (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as e
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as f;

which will generate the first million numbers. If you need only the positive numbers, simply add + 1to the expression.

这将生成前一百万个数字。如果您只需要正数，只需添加+ 1到表达式中。

Note that in MySQL in particular, the results may not be sorted. You need to append order by nto the end if you need ordered numbers. This will increase the execution time dramatically, though (on my machine, it jumped up from 5 ms to 500 ms).

请注意，特别是在 MySQL 中，结果可能不会排序。order by n如果您需要订购号码，则需要附加到末尾。不过，这会显着增加执行时间（在我的机器上，它从 5 毫秒跳到 500 毫秒）。

For simple queries, here's a query for just the first 10000 numbers:

对于简单的查询，这里只查询前 10000 个数字：

select (a.digit + (10 * b.digit) + (100 * c.digit) + (1000 * d.digit)) as n
    from (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as digit union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d;

This answer is adapted from the following query that returns a date range: https://stackoverflow.com/a/2157776/2948

此答案改编自以下返回日期范围的查询：https: //stackoverflow.com/a/2157776/2948

回答by Garry

This may help

这可能有帮助

To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j – i)). For example, to obtain a random integer in the range the range 7 <= R < 12, you could use the following statement:

要获得 i <= R < j 范围内的随机整数 R，请使用表达式 FLOOR(i + RAND() * (j – i))。例如，要获得 7 <= R < 12 范围内的随机整数，您可以使用以下语句：

SELECT FLOOR(7 + (RAND() * 5));

SELECT FLOOR(7 + (RAND() * 5));

回答by Steven LeVander

If your database supports analytic windowing functions the following is simple works really well:

如果您的数据库支持分析窗口函数，则以下简单的工作非常好：

SELECT  row_number() over (partition by 1 order by 1) numbers
FROM SOME_TABLE
LIMIT 2700;

This statement returns a set of numbers from 1 to 2700.

此语句返回一组从 1 到 2700 的数字。

回答by Frank Bollack

Take a look at the the followhing SO questions:

看看以下SO问题：

• How to generate a range of numbers in Mysql
• generate an integer sequence in MySQL

• 如何在Mysql中生成一系列数字
• 在 MySQL 中生成整数序列

Edit:

编辑：

Another aproach is to create a stored procedure that does that for you. PostgreSQL contains a function generate_series(start, stop) that does what you want.

另一种方法是创建一个为您执行此操作的存储过程。PostgreSQL 包含一个函数 generate_series(start, stop) 可以执行您想要的操作。

select * from generate_series(2,4);
 generate_series
-----------------
               2
               3
               4
(3 rows)

I'm not familar with MySQL but somthing like that should be easy to implement, if you are okay with SPs. Thissite shows an implemetation.

我不熟悉 MySQL，但如果您对 SP 没问题，那么类似的东西应该很容易实现。 该站点显示了一个实现。

回答by Pete

I' pretty sure that you cannot do it, if I understand your question correctly.

如果我正确理解你的问题，我很确定你不能这样做。

As I understand your question, you want the list, from a single SQL statement, without having to reference a specific table?

据我了解您的问题，您想要一个来自单个 SQL 语句的列表，而不必引用特定的表？

I'm pretty sure that it is not possible in any SQL dialect. If you were to get a sequentially incrementing number along with the results of another query, then that would be possible (depending on SQL dialect, on mssql it would be rownumber(), but I don't know how in MySql, but it's probably there)

我很确定这在任何 SQL 方言中都是不可能的。如果您要获得一个顺序递增的数字以及另一个查询的结果，那么这是可能的（取决于 SQL 方言，在 mssql 上它将是 rownumber()，但我不知道在 MySql 中如何，但它可能是那里）

But that's not what I hear you ask?

但这不是我听到你问的？