SQL SELECT获取前N个正整数 [英] SQL SELECT to get the first N positive integers

问题描述

我需要获取一个包含前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.

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

这篇关于SQL SELECT获取前N个正整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！