断层描述:
断层的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执行结果: