1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- 创建表
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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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;

1
2
3
4
5
6
7
8
9
10
11
12
13

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 <= t1.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;

1
2
3
4
5
6
7

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;

1
2
3
4
5
6
7
8
9

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;

1
2
3
4
5
6
7
8
9

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;

1
2
3
4
5
6
7
8

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;