斷層描述：
斷層的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執行結果：

總結

以上是生活随笔為你收集整理的MYSQL数据累加后出现断层问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。