How Can I Generate a Sequence of Numbers Between Two Given Values Using SQL?

Generating Number Ranges with SQL

Need to create a sequence of numbers between two specified values (e.g., 1000 and 1050) within a SQL database? This article demonstrates efficient methods using VALUES and JOIN operations.

The core idea is to generate a set of numbers using VALUES and then use JOIN to combine these into a larger range. This approach easily scales to generate hundreds of thousands of rows by expanding the query.

Here's a compact solution:

<code class="language-sql">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</code>

A more explicit method is:

<code class="language-sql">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</code>

Both examples can be customized to generate specific ranges by adding a WHERE clause. For frequent use, consider creating a table-valued function for reusability.

The above is the detailed content of How Can I Generate a Sequence of Numbers Between Two Given Values Using SQL?. For more information, please follow other related articles on the PHP Chinese website!