How Can I Generate a Range of Numbers in SQL Using Only Queries?

Pure SQL query to generate numeric sequence

In SQL, using non-persistent values ​​(VALUES keyword) and JOIN operations, you can generate a sequence of numbers within a specified range.

Simple solution:

WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1

This scheme generates a number range by creating a Cartesian product of four values ​​representing units, tens, hundreds, and thousands. The ROW_NUMBER() function assigns unique row numbers in the desired order.

Detailed plan:

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

This scheme calculates numerical values ​​explicitly by adding values ​​in units, tens, hundreds and thousands places. Both scenarios can be expanded with a WHERE clause to specify a range of numbers. For larger scopes, table-valued functions can be created to reuse code.

The above is the detailed content of How Can I Generate a Range of Numbers in SQL Using Only Queries?. For more information, please follow other related articles on the PHP Chinese website!