How Can I Generate a Series of Numbers Using SQL Queries?

Use SQL query to generate number sequence

When dealing with numerical data, it is often necessary to generate a range of numbers between two specific values. SQL makes this easy, allowing you to explicitly specify a range of numbers or use a subquery.

One way is to use the VALUES keyword to create a series of non-persistent values. By using cross-joins to join these values, a large number of combinations can be generated. This method is simple and efficient.

For example, if you have two input numbers, say 1000 and 1050, you can generate the entire sequence using the following query:

<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 query will generate a sequence of numbers from 1000 to 1050, with each number occupying one row. Alternatively, you can create a more detailed version of your query:

<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 queries can be extended to a specific range of numbers by adding a WHERE clause. To improve reusability, you can also define a table-valued function to generate a numeric range.

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