[TOC] # 需求 有如下訪客訪問次數統計表 t_access_times  需要輸出報表：t_access_times_accumulate  # 實現步驟 1. 第一步，先求個用戶的月總金額 ~~~ select username,month,sum(salary) as salary from t_access_times group by username,month ~~~ ~~~ +-----------+----------+---------+--+ | username | month | salary | +-----------+----------+---------+--+ | A | 2015-01 | 33 | | A | 2015-02 | 10 | | B | 2015-01 | 30 | | B | 2015-02 | 15 | +-----------+----------+---------+--+ ~~~ 2. 第二步，將月總金額表 自己連接 自己連接 ~~~ select A.*,B.* FROM (select username,month,sum(salary) as salary from t_access_times group by username,month) A inner join (select username,month,sum(salary) as salary from t_access_times group by username,month) B on A.username=B.username where B.month <= A.month ~~~ ~~~ +-------------+----------+-----------+-------------+----------+-----------+--+ | a.username | a.month | a.salary | b.username | b.month | b.salary | +-------------+----------+-----------+-------------+----------+-----------+--+ | A | 2015-01 | 33 | A | 2015-01 | 33 | | A | 2015-01 | 33 | A | 2015-02 | 10 | | A | 2015-02 | 10 | A | 2015-01 | 33 | | A | 2015-02 | 10 | A | 2015-02 | 10 | | B | 2015-01 | 30 | B | 2015-01 | 30 | | B | 2015-01 | 30 | B | 2015-02 | 15 | | B | 2015-02 | 15 | B | 2015-01 | 30 | | B | 2015-02 | 15 | B | 2015-02 | 15 | +-------------+----------+-----------+-------------+----------+-----------+--+ ~~~ 3. 第三步，從上一步的結果中 進行分組查詢，分組的字段是`a.username a.month` 求月累計值： 將`b.month <= a.month`的所有`b.salary`求和即可 ~~~ select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate from (select username,month,sum(salary) as salary from t_access_times group by username,month) A inner join (select username,month,sum(salary) as salary from t_access_times group by username,month) B on A.username=B.username where B.month <= A.month group by A.username,A.month order by A.username,A.month; ~~~