MySQL自动对比当月截至当前与前两月同期采购总额

Hey there, sounds like you're tired of manually inputting date ranges to compare your current month's purchase totals with the same periods from the last two months. Let's fix that with some handy MySQL date functions that'll handle the date calculations automatically.

Option 1: Aggregate All Totals in a Single Row #

This query returns totals for the current month-to-date, last month's same period, and the month before that all in one result row—perfect for quick side-by-side comparisons:

SELECT
    -- Current month: from first day to today
    SUM(CASE 
        WHEN purchase_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE() 
        THEN amount 
        ELSE 0 
    END) AS current_month_total,
    -- Last month: same date range as current
    SUM(CASE 
        WHEN purchase_date BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
        THEN amount 
        ELSE 0 
    END) AS last_month_total,
    -- Month before last: same date range as current
    SUM(CASE 
        WHEN purchase_date BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01') AND DATE_SUB(CURDATE(), INTERVAL 2 MONTH) 
        THEN amount 
        ELSE 0 
    END) AS two_months_ago_total
FROM
    purchases; -- Replace with your actual table name

Option 2: Separate Rows for Each Period #

If you prefer each period's total on its own row (easier to read in some reporting tools), use UNION ALL to combine three targeted queries:

-- Current month to date
SELECT
    'Current Month' AS period_label,
    SUM(amount) AS total_purchases
FROM
    purchases
WHERE
    purchase_date BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01') AND CURDATE()

UNION ALL

-- Last month's matching period
SELECT
    'Last Month Same Period' AS period_label,
    SUM(amount) AS total_purchases
FROM
    purchases
WHERE
    purchase_date BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01') AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

UNION ALL

-- Month before last's matching period
SELECT
    'Two Months Ago Same Period' AS period_label,
    SUM(amount) AS total_purchases
FROM
    purchases
WHERE
    purchase_date BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m-01') AND DATE_SUB(CURDATE(), INTERVAL 2 MONTH);

Quick Breakdown of the Date Functions #

These are the workhorses making the automation possible:

• CURDATE(): Grabs today's date (no time component, ideal for date-range filters).
• DATE_SUB(CURDATE(), INTERVAL N MONTH): Subtracts N months from today to get the same day in the previous month(s). MySQL automatically handles edge cases like March 31st → February 28th/29th.
• DATE_FORMAT(date, '%Y-%m-01'): Converts any date to the first day of its month, giving us the start of our target period.

Just swap purchases with your actual table name and amount with your purchase total column, and you're good to go—no more manual date typing.

内容的提问来源于stack exchange，提问作者Osoba Osaze