How Can I Generate the First N Positive Integers with a SQL SELECT Statement?

Generating the First N Positive Integers Using SQL SELECT

Problem:

Obtaining the first N positive integers using solely a standard SQL SELECT statement has posed a challenge. Is there a workaround without relying on a count table?

Answer:

While general SQL lacks a native method for this operation, several major database systems offer solutions:

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:

Unlike the aforementioned systems, MySQL lacks a similar mechanism. As a workaround, you can use the following script to create a temporary table and fill it with the desired numbers:

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
$$

To use the script, call the procedure with the desired number of integers:

CALL prc_filler(10);

The above is the detailed content of How Can I Generate the First N Positive Integers with a SQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!