How Can SQL Generate a Range of Numbers Between Two Specified Values?

SQL: Generating Number Sequences Between Two Values

Need to create a list of consecutive numbers within a specified range in SQL? This is easily achievable using a combination of the VALUES keyword and JOIN operations. Let's say you need to generate a sequence from 1000 to 1050, each number on a new line:

<code>1000
1001
1002
1003
...
1050</code>

Here's how to do it:

The Solution:

This method leverages multiple JOINs of a small numbers table to create a larger sequence.

WITH RECURSIVE x AS (
  SELECT 0 AS n
  UNION ALL
  SELECT n + 1
  FROM x
  WHERE n < 9999 -- Adjust upper limit as needed
)
SELECT n
FROM x
WHERE n BETWEEN 1000 AND 1050
ORDER BY n;

Explanation:

1. Recursive CTE (Common Table Expression): A WITH RECURSIVE statement creates a temporary table x containing numbers from 0 upwards. The UNION ALL combines the initial 0 with subsequent numbers, incrementing until the condition n < 9999 is no longer met. Adjust 9999 to control the maximum number generated.

2. Filtering the Range: The WHERE clause filters the results from the CTE to only include numbers between 1000 and 1050 (inclusive).

3. Ordering: ORDER BY n ensures the numbers are displayed in ascending order.

Alternative (Non-Recursive): For smaller ranges, a non-recursive approach might be simpler:

WITH x AS (
    SELECT 1000 AS num UNION ALL
    SELECT 1001 UNION ALL
    SELECT 1002 UNION ALL
    -- ... add more numbers as needed ...
    SELECT 1050
)
SELECT num FROM x;

However, the recursive approach is far more scalable for larger ranges.

Conclusion:

This SQL technique provides a flexible and efficient way to generate numerical sequences within defined boundaries. The recursive CTE offers scalability for generating very large sequences, making it a powerful tool for various data generation tasks. Remember to adjust the upper limit in the recursive CTE (n < 9999) to accommodate the desired range.

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