思路
累积求和其实就是通过inner join表本身来实现。首先要先明白 统计每个用户每个月总共获得多少小费 的hql语句怎么写SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month;
+-----------+----------+------+--+
| username | month | _c2 |
+-----------+----------+------+--+
| A | 2015-01 | 33 |
| A | 2015-02 | 10 |
| A | 2015-03 | 16 |
| B | 2015-01 | 30 |
| B | 2015-02 | 15 |
| B | 2015-03 | 17 |
+-----------+----------+------+--+
现在通过inner join连接自己SELECT a.*,b.*
FROM (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username;
+-------------+----------+--------+-------------+----------+--------+--+
| a.username | a.month | a._c2 | b.username | b.month | b._c2 |
+-------------+----------+--------+-------------+----------+--------+--+
| A | 2015-01 | 33 | A | 2015-01 | 33 |
| A | 2015-01 | 33 | A | 2015-02 | 10 |
| A | 2015-01 | 33 | A | 2015-03 | 16 |
| A | 2015-02 | 10 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-02 | 10 |
| A | 2015-02 | 10 | A | 2015-03 | 16 |
| A | 2015-03 | 16 | A | 2015-01 | 33 |
| A | 2015-03 | 16 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-03 | 16 |
| B | 2015-01 | 30 | B | 2015-01 | 30 |
| B | 2015-01 | 30 | B | 2015-02 | 15 |
| B | 2015-01 | 30 | B | 2015-03 | 17 |
| B | 2015-02 | 15 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-02 | 15 |
| B | 2015-02 | 15 | B | 2015-03 | 17 |
| B | 2015-03 | 17 | B | 2015-01 | 30 |
| B | 2015-03 | 17 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-03 | 17 |
+-------------+----------+--------+-------------+----------+--------+--+
得到以上结果可以看出，我们可以利用b表的salary列来进行累积求和，因为我们要求每个用户每个月总共获得小费，到2月份是要将2月和前一个月的小费累计，也就是表中33,33+10,33+10+16这样SELECT a.*,b.*
FROM (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT username,month,sum(salary)
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username
WHERE b.month <= a.month;
+-------------+----------+--------+-------------+----------+--------+--+
| a.username | a.month | a._c2 | b.username | b.month | b._c2 |
+-------------+----------+--------+-------------+----------+--------+--+
| A | 2015-01 | 33 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-01 | 33 |
| A | 2015-02 | 10 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-01 | 33 |
| A | 2015-03 | 16 | A | 2015-02 | 10 |
| A | 2015-03 | 16 | A | 2015-03 | 16 |
| B | 2015-01 | 30 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-01 | 30 |
| B | 2015-02 | 15 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-01 | 30 |
| B | 2015-03 | 17 | B | 2015-02 | 15 |
| B | 2015-03 | 17 | B | 2015-03 | 17 |
+-------------+----------+--------+-------------+----------+--------+--+
得到以上结果后，只需要按照Month分组，对salary列用SUM函数即可SELECT SUM(bSalSum)
FROM (
SELECT a.month AS aMonth,a.username AS aUser,a.salSum AS aSalSum,
b.month AS bMonth,b.username AS bUser,b.salSum AS bSalSum
FROM (
SELECT month,username,SUM(salary) AS salSum
FROM t_salary_detail
GROUP BY username,month ) a
INNER JOIN (
SELECT month,username,SUM(salary) AS salSum
FROM t_salary_detail
GROUP BY username,month ) b
ON a.username = b.username
WHERE b.month <= a.month ) t
GROUP BY aUser,aMonth;
+------+--+
| _c0 |
+------+--+
| 33 |
| 43 |
| 59 |
| 30 |
| 45 |
| 62 |
+------+--+
