如何编写SQL查询返回连续7天结果：含无数据/跨月场景

Got it, let's work through this problem step by step. The core challenge here is ensuring we return results for every day in a 7-day window, even if there are no matching jobs on a given day or the day falls in a different month. Here's how to approach it:

Step 1: Generate a Continuous 7-Day Date Sequence #

First, we need to create a list of 7 consecutive dates—this acts as our "base" to ensure we don't miss any days. Most modern databases support recursive CTEs (Common Table Expressions) for this, which is clean and flexible.

Step 2: Join with Jobs Table & Aggregate Data #

We'll left-join this date sequence with the jobs table to include all days, then calculate the total job_amount for each day (handling NULL values appropriately).

Example SQL (MySQL 8.0+/PostgreSQL) #

WITH RECURSIVE date_range AS (
    -- Define the start of your 7-day window (adjust this as needed)
    SELECT CURRENT_DATE - INTERVAL '6 days' AS date_day
    UNION ALL
    SELECT date_day + INTERVAL '1 day'
    FROM date_range
    -- Stop when we reach the end of the 7-day window
    WHERE date_day + INTERVAL '1 day' <= CURRENT_DATE
)
SELECT
    dr.date_day,
    -- Replace NULL totals with 0 if no jobs exist for the day
    COALESCE(SUM(j.job_amount), 0) AS total_job_amount
FROM date_range dr
-- Join jobs where the created_at date matches our sequence date
LEFT JOIN jobs j ON DATE(j.created_at) = dr.date_day
GROUP BY dr.date_day
ORDER BY dr.date_day;

Key Details: #

• Recursive CTE (date_range): This generates 7 consecutive dates starting from 6 days before today up to today. You can modify the start/end dates—for example, replace CURRENT_DATE - INTERVAL '6 days' with a specific start date like '2018-04-16' if you need a fixed window.
• Left Join: Ensures every date in our sequence is included, even if there are no matching jobs in jobs.
• COALESCE & SUM: SUM(j.job_amount) ignores NULL values in job_amount, and COALESCE converts any NULL total (from days with no jobs) to 0 for clarity.
• Cross-Month Support: The date sequence will naturally include days from adjacent months if the 7-day window spans a month boundary—no extra work needed here!

For Older MySQL Versions (Pre-8.0, No Recursive CTE) #

If you're stuck on MySQL 5.x, you can generate the date sequence using a manual numbers list:

SELECT
    date_day,
    COALESCE(SUM(j.job_amount), 0) AS total_job_amount
FROM (
    -- Manually generate 7 consecutive dates
    SELECT DATE('2018-04-16') + INTERVAL (n-1) DAY AS date_day
    FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
          SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) AS numbers
) AS date_range
LEFT JOIN jobs j ON DATE(j.created_at) = date_range.date_day
GROUP BY date_day
ORDER BY date_day;

Just replace '2018-04-16' with your desired start date to adjust the 7-day window.

内容的提问来源于stack exchange，提问作者Jasjeet Singh