如何查询上月同日销售额及上月当前周销售额？含SQL实现方法

Hey there! Let’s walk through exactly how to pull the sales data you need—both for the same day last month and the matching week period from last month, with practical SQL examples tailored to common databases.

The core idea here is to calculate the date exactly one month prior to today, then filter your sales table to only include records from that date. We’ll also cover edge cases like month-end dates (e.g., March 31st → February 28th/29th).

MySQL Example #

-- Basic query for same day last month
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    DATE(sale_date) = DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

-- Handle month-end edge cases (e.g., 31st → last day of previous month)
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    DATE(sale_date) = CASE
        WHEN DAY(CURDATE()) > DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
        THEN LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
        ELSE DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
    END;

• CURDATE() gets today’s date, DATE_SUB subtracts one month.
• The second query checks if today’s day doesn’t exist in the previous month (like 31st in April), then uses LAST_DAY to get the final day of last month instead.

PostgreSQL Example #

-- Basic query for same day last month
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    sale_date::DATE = CURRENT_DATE - INTERVAL '1 month';

-- Handle month-end edge cases
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    sale_date::DATE = LEAST(
        CURRENT_DATE - INTERVAL '1 month',
        (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day')::DATE
    );

• CURRENT_DATE - INTERVAL '1 month' calculates the prior month’s same date.
• LEAST ensures we don’t end up with an invalid date (e.g., March 31st → February 28th instead of March 2nd).

SQL Server Example #

-- Basic query for same day last month
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    CAST(sale_date AS DATE) = CAST(DATEADD(month, -1, GETDATE()) AS DATE);

-- Handle month-end edge cases
SELECT 
    SUM(sales_amount) AS last_month_same_day_sales
FROM 
    sales
WHERE 
    CAST(sale_date AS DATE) = CASE
        WHEN DAY(GETDATE()) > DAY(EOMONTH(DATEADD(month, -1, GETDATE())))
        THEN EOMONTH(DATEADD(month, -1, GETDATE()))
        ELSE CAST(DATEADD(month, -1, GETDATE()) AS DATE)
    END;

• DATEADD(month, -1, GETDATE()) subtracts one month from today.
• EOMONTH returns the last day of the previous month for edge cases.

First, we need to define the start and end dates of the current week, then shift those dates back by one month to get the matching period from last month. Note: Week start/end can vary (Monday vs Sunday) — we’ll use Monday as the start here, with adjustments for other conventions.

MySQL Example (Monday to Sunday week) #

-- Define current week's start and end
SET @current_week_start = CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY; -- Monday
SET @current_week_end = CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY; -- Sunday

-- Get sales from the corresponding week last month
SELECT 
    SUM(sales_amount) AS last_month_corresponding_week_sales
FROM 
    sales
WHERE 
    sale_date BETWEEN DATE_SUB(@current_week_start, INTERVAL 1 MONTH) 
                  AND DATE_SUB(@current_week_end, INTERVAL 1 MONTH);

• WEEKDAY(CURDATE()) returns 0 for Monday, so subtracting that gives the start of the week.
• Adjust WEEKDAY to DATE_FORMAT(CURDATE(), '%w') if your week starts on Sunday (0 = Sunday).

PostgreSQL Example (Monday to Sunday week) #

WITH current_week AS (
    SELECT 
        -- Calculate Monday start
        CURRENT_DATE - (EXTRACT(DOW FROM CURRENT_DATE) - 1)::INTEGER AS week_start,
        -- Calculate Sunday end
        CURRENT_DATE + (7 - EXTRACT(DOW FROM CURRENT_DATE))::INTEGER AS week_end
)
SELECT 
    SUM(s.sales_amount) AS last_month_corresponding_week_sales
FROM 
    sales s
CROSS JOIN current_week cw
WHERE 
    s.sale_date::DATE BETWEEN cw.week_start - INTERVAL '1 month' 
                          AND cw.week_end - INTERVAL '1 month';

• EXTRACT(DOW FROM CURRENT_DATE) returns 0 for Sunday, so we adjust to get Monday as the start.
• For a Sunday-start week, remove the -1 from the week_start calculation.

SQL Server Example (Monday to Sunday week) #

-- Set Monday as the first day of the week (default is Sunday)
SET DATEFIRST 1;

DECLARE @current_week_start DATE = DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE());
DECLARE @current_week_end DATE = DATEADD(day, 7 - DATEPART(weekday, GETDATE()), GETDATE());

SELECT 
    SUM(sales_amount) AS last_month_corresponding_week_sales
FROM 
    sales
WHERE 
    CAST(sale_date AS DATE) BETWEEN DATEADD(month, -1, @current_week_start) 
                                AND DATEADD(month, -1, @current_week_end);

• SET DATEFIRST 1 ensures DATEPART(weekday) returns 1 for Monday.
• Omit this line if your week starts on Sunday (default behavior).

内容的提问来源于stack exchange，提问作者ankita pagdhare