mysql 按月分组累计统计数据，纯sql实现的一个方法

转载至：
https://blog.csdn.net/guoleiding888/article/details/54426052

在网上查阅了很多方法，我尝试了用后台java，但实现起来比较麻烦，最后自己探索用纯sql来实现，记录起来分享下。

mysql 按月分组累计统计数据，想得到如下的结果，如下图：

【year_and_month】- 显示按月分组查询的字段

【count_per_month】- 显示每月分组统计的数量
【total_by_mont】- 显示按月分组累计总数 = 本月分组统计数量 + 本月前所有月份分组统计数量

我的解决思路：

1、首先要准备或得到一个如下图结果的表 “test_table” ：

2、执行一下sql

SELECT
year_and_moth,
count_per_month,
(
SELECT
SUM(count_per_month)
FROM
test_table AS test_table_1
WHERE
test_table_1.year_and_moth <= test_table_2.year_and_moth
) AS total_by_month
FROM
test_table AS test_table_2

4、举例2

WITH temp AS (
SELECT SUBSTR(T.entry_time FROM 1 FOR 7) AS cal_month, COUNT(1) AS per_month
FROM ziguang-personnal.employee T
WHERE T.status = 1
AND YEAR(NOW())>= SUBSTR(T.entry_time FROM 1 FOR 4)
GROUP BY SUBSTR(T.entry_time FROM 1 FOR 7)
ORDER BY SUBSTR(T.entry_time FROM 1 FOR 7)
)
, tbl AS (
SELECT cal_month, per_month,
(SELECT SUM(per_month) FROM temp AS A
WHERE A.cal_month <= B.cal_month
) AS ‘人数’
FROM temp AS B
)
,last_year_tbl AS (
SELECT SUBSTR(cal_month FROM 6 FOR 2) AS cal_month, per_month, 人数 AS ‘去年人数’
FROM tbl
WHERE YEAR(NOW()) -1 = SUBSTR(cal_month FROM 1 FOR 4)
)
,now_year_tbl AS (
SELECT SUBSTR(cal_month FROM 6 FOR 2) AS cal_month, per_month, 人数 AS ‘今年人数’
FROM tbl
WHERE YEAR(NOW()) = SUBSTR(cal_month FROM 1 FOR 4)
)
SELECT A.cal_month AS ‘月份’, A.去年人数, B.今年人数
FROM last_year_tbl A
LEFT JOIN now_year_tbl B ON A.cal_month = B.cal_month

WITH temp AS (
SELECT SUBSTR(T.entry_time FROM 1 FOR 7) AS cal_month, COUNT(1) AS per_month
FROM ziguang-personnal.employee T
WHERE T.status = 1
AND YEAR(NOW()) - 1 >= SUBSTR(T.entry_time FROM 1 FOR 4)
GROUP BY SUBSTR(T.entry_time FROM 1 FOR 7)
ORDER BY SUBSTR(T.entry_time FROM 1 FOR 7)
)
, tbl AS (
SELECT cal_month, per_month,
(SELECT SUM(per_month) FROM temp AS A
WHERE A.cal_month <= B.cal_month
) AS ‘去年人数’
FROM temp AS B
)

SELECT SUBSTR(cal_month FROM 6 FOR 2) AS cal_month, per_month, 去年人数
FROM tbl
WHERE YEAR(NOW()) -1 = SUBSTR(cal_month FROM 1 FOR 4)