MYSQL数据累加后出现断层问题

断层描述：
断层的SQL为：
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
ORDER BY A.cal_month

断层SQL执行结果为：

断层原因为：原始数据中没有今年的5月份数据，导致数据统计为NULL

断层解决方案：
构造一条5月份的数据，值为0，然后与原始数据UNION ALL后，将原始数据与构造数据进行SUM处理。

解决后的SQL:
WITH temp AS (
SELECT cal_month, SUM(per_month) AS per_month
FROM(
SELECT SUBSTR(dt1 FROM 1 FOR 7) AS cal_month, 0 AS per_month FROM(
SELECT
@dt1 as dt1,
@dt1 := @dt1 + INTERVAL 1 MONTH,
@dt := @dt1 - INTERVAL 1 DAY,
CASE WHEN @dt1 < @dt2 THEN @dt ELSE @dt2 END as dt2
FROM( SELECT @dt1:=CAST(CONCAT(YEAR(NOW())-1,’-01-01’) as date), @dt2:=CAST(SUBSTR(NOW() FROM 1 FOR 11) as date) ) _x,
information_schema.columns A
WHERE @dt1 < @dt2
) A
UNION ALL
(
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)
)
)A
GROUP BY cal_month
)
, 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
ORDER BY A.cal_month

解决后的SQL执行结果：