How to Generate a Range of Numbers in SQL Between Specified Parameters?

Generating Sequential Number Series in SQL

This article demonstrates how to generate a sequence of numbers within a specified range in SQL. Imagine needing a list of numbers from 1000 to 1050, each on a separate row.

Method 1: VALUES and Cross Joins

One technique uses the VALUES clause to create temporary number sets and combines them via cross joins.

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

This generates numbers 0-9999. Adjusting the VALUES clause limits the output to the desired range.

Method 2: Individual Digit Tables

A more explicit approach uses separate tables for ones, tens, hundreds, and thousands places.

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

This offers precise control over the number generation process and is easily scalable for larger ranges. Both methods provide flexible solutions for creating numerical sequences in SQL, adaptable to different needs.

The above is the detailed content of How to Generate a Range of Numbers in SQL Between Specified Parameters?. For more information, please follow other related articles on the PHP Chinese website!