SQL SELECT 获取前 N 个正整数

2021-11-20 00:00:00 sql mysql

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

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)?

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

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

推荐答案

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

Seems that what you want is a dummy rowset.

在MySQL中，没有表是不可能的.

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

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

Most major systems provide a way to do it:

  • 在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

    • MySQL 缺少这样的东西，这是一个严重的缺点.

    MySQL lacks something like this and this is a serious drawback.

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

    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.

    相关文章