mysql按月分组算差

SELECT 
    YEAR(date) AS year,
    MONTH(date) AS month,
    amount - (
        SELECT MIN(amount)
        FROM sales
        WHERE YEAR(date) = s.year AND MONTH(date) = s.month
    ) AS cumulative_difference
FROM 
    sales s
GROUP BY 
    YEAR(date), MONTH(date)
ORDER BY 
    year, month;

SELECT 
    s1.year,
    s1.month,
    s1.amount - s2.amount AS monthly_difference
FROM 
    sales s1
JOIN 
    sales s2 
ON 
    s1.year = s2.year AND s1.month = s2.month + 1
ORDER BY 
    s1.year, s1.month;

UPDATE sales SET date = STR_TO_DATE(date, '%Y-%m-%d');

SELECT 
    YEAR(s.date) AS year,
    MONTH(s.date) AS month,
    COALESCE(s.amount - IFNULL(lag_amount, 0), 0) AS monthly_difference
FROM 
    sales s
LEFT JOIN (
    SELECT 
        YEAR(date) AS year,
        MONTH(date) AS month,
        amount AS lag_amount
    FROM 
        sales
    GROUP BY 
        YEAR(date), MONTH(date)
) prev ON s.year = prev.year AND s.month = prev.month + 1
ORDER BY 
    year, month;