MySQL中按月统计并逐月累加统计值的几种写法

-- 创建表 CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sales_date` date NOT NULL, `sales_amount` decimal(10,2) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 插入模拟数据 INSERT INTO `sales` (`sales_date`, `sales_amount`) VALUES ('2023-01-01', 1500.00), ('2023-01-02', 1800.00), ('2023-01-05', 2200.00), ('2023-02-01', 1200.00), ('2023-02-03', 1800.00), ('2023-03-01', 2500.00), ('2023-03-05', 2800.00), ('2023-03-08', 3200.00), ('2023-04-01', 2100.00), ('2023-04-03', 1900.00), ('2023-04-05', 2600.00), ('2023-05-01', 3100.00), ('2023-05-02', 3400.00), ('2023-06-01', 3800.00), ('2023-06-06', 4200.00);

SELECT t1.month, t1.monthly_sales, SUM(t2.monthly_sales) AS cumulative_sales FROM ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) t1 JOIN ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) t2 ON t1.month >= t2.month GROUP BY t1.month;

SELECT month, monthly_sales, (SELECT SUM(monthly_sales) FROM ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) t2 WHERE t2.month

SELECT month, monthly_sales, @cumulative := @cumulative + monthly_sales AS cumulative FROM ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) t1 CROSS JOIN (SELECT @cumulative := 0) t2;

SELECT month, monthly_sales, (@cumulative := @cumulative + monthly_sales) AS cumulative_sales FROM ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ORDER BY sales_date ASC ) t1, (SELECT @cumulative := 0) t2;

WITH monthly_sales AS ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) SELECT month, monthly_sales, SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales FROM monthly_sales;

SELECT month, monthly_sales, SUM(monthly_sales) OVER (ORDER BY month) AS cumulative_sales FROM ( SELECT DATE_FORMAT(sales_date, '%Y-%m') AS month, SUM(sales_amount) AS monthly_sales FROM sales GROUP BY month ) t1;