How to generate a series of numbers or dates in SQL?

There are three main ways to generate a number or date sequence in SQL. 1. Use the generate_series() function in PostgreSQL to quickly generate numeric and date sequences, such as SELECT generate_series(1, 10) to generate numeric numbers from 1 to 10, or use date parameters to generate a specified range of dates; 2. Use recursive CTE in SQL Server or MySQL to manually build numeric or date ranges through recursive methods, such as using WITH statements to generate numeric sequences from 1 to 10 or consecutive dates; 3. Use pre-built numeric tables or calendar tables, suitable for all databases, use existing Numbers tables to generate required ranges from DATEADD and other functions, or directly query the calendar table to improve performance and facilitate analysis. In addition, you need to pay attention to performance and limitations, such as recursive depth limitation, avoiding excessive inline generation, and filtering as early as possible to reduce overhead.

In SQL, generating a series of numbers or dates is something you might need for populating test data, reporting purposes, or joining with existing tables. While SQL doesn't have a built-in range() function like Python, there are several practical ways to accomplish this depending on your database system.

Use generate_series() in PostgreSQL

If you're working with PostgreSQL , the easiest and most powerful method is using the built-in generate_series() function. It can generate sequences of both numbers and dates.

For example, to generate a list of numbers from 1 to 10:

 SELECT generate_series(1, 10);

To generate a sequence of dates — say, every day in January 2024:

 SELECT generate_series('2024-01-01'::date, '2024-01-31'::date, '1 day');

This function is fast and flexible, especially when used in joins or subqueries.

Use a recursive CTE in SQL Server or MySQL

If your database doesn't support generate_series() , such as SQL Server or MySQL , you can use a recursive Common Table Expression (CTE) to build number or date ranges manually.

Here's how to create a number series from 1 to 10 in SQL Server:

 WITH Numbers AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num 1 FROM Numbers WHERE num < 10
)
SELECT num FROM Numbers;

For dates, just add a starting date and increment by one day:

 WITH Dates AS (
    SELECT CAST('2024-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(day, 1, dt) FROM Dates WHERE dt < &#39;2024-01-10&#39;
)
SELECT dt FROM Dates;

You'll want to be careful with recursion limits — some systems cap the number of iterations unless you adjust settings.

Use a numbers table or calendar table

Another approach that works across all databases is having a prebuilt numbers table or calendar table in your database. These are especially useful if you frequently need to generate ranges.

A simple numbers table might look like this:

 CREATE TABLE Numbers (
    num INT PRIMARY KEY
);

Then fill it with values from 1 to 10000 or more. Once you have that, selecting a range becomes easy:

 SELECT num FROM Numbers WHERE num BETWEEN 1 AND 50;

For dates, you can either calculate based on a start date:

 SELECT DATEADD(day, num - 1, '2024-01-01') AS dt
FROM Numbers
WHERE num <= DATEDIFF(day, &#39;2024-01-01&#39;, &#39;2024-01-31&#39;) 1;

Or better yet, maintain a full calendar table with precomputed dates, which is great for reporting and time-based analysis.

Watch out for performance and limits

When generating large series, especially with recursive CTEs, performance can degrade if not handled carefully. Some things to keep in mind:

• Recursive depth is limited in many databases unless explicitly configured.
• Avoid generating huge ranges inline; consider storing them in a helper table instead.
• If you're using these ranges for joins, make sure to filter early to reduce overhead.

Also, always check your specific database's documentation — functions and syntax may vary slightly between systems like Oracle, SQLite, etc.

That's about it. Depending on your SQL dialect and needs, you've got options ranged from quick one-liners to reusable structures. Not too bad once you know the right tools.

The above is the detailed content of How to generate a series of numbers or dates in SQL?. For more information, please follow other related articles on the PHP Chinese website!