博客

MYSQL查询：统计分别统计目标月份数据，将查询的结果作为查询表进行关联，如果查询结果为null赋值0，分别求出每个月的数据，并算出同比增长率和环比增长率。

需要分别用到以下：

-- 如果sum的值为null赋值0
IFNULL(sum,0)

-- 求取增长率并保留两位小数
CONCAT( ROUND((IFNULL(t.sum,0)-IFNULL(t1.sum,0)) / IFNULL(t1.sum,1) * 100, 2 ), '', '%' )

-- 本月第一天
CONCAT(DATE_FORMAT(LAST_DAY(NOW()),'%Y-%m-'),'01')
-- 本月最后一天
LAST_DAY(NOW())
-- 上个月第一天
CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),'%Y-%m-'),'01')
-- 上个月最后
LAST_DAY(NOW() - INTERVAL 1 MONTH)
-- 上一年当前月份第一天
CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 YEAR),'%Y-%m-'),'01') 
-- 上一年当前月份最后一天
LAST_DAY(NOW() - INTERVAL 1 YEAR)
-- 其它写法：
-- 本月第一天 RIGHT返回字符串最右边的2个字符就加上了0
CONCAT(YEAR(NOW()),'-',RIGHT(100 + MONTH(NOW()), 2),'-01')
-- 本月最后一天
CONCAT(YEAR(NOW()),'-',RIGHT(100 + MONTH(NOW()), 2),'-31')
-- 上个月第一天
CONCAT(YEAR(NOW()),'-',RIGHT(100 + MONTH(NOW())-1, 2),'-01') 
-- 上个月最后一天
CONCAT(YEAR(NOW()),'-',RIGHT(100 + MONTH(NOW())-1, 2),'-31')
-- 上一年当前月份第一天
CONCAT(YEAR(NOW())-1,'-',RIGHT(100 + MONTH(NOW()), 2),'-01'
-- 上一年当前月份最后一天
CONCAT(YEAR(NOW())-1,'-',RIGHT(100 + MONTH(NOW()), 2),'-31')

其他时间截取：

-- LPAD函数从左边对字符串使用指定的字符进行填充
CONCAT(LPAD(MONTH(TIME), 2, '0')

整体SQL:

SELECT IFNULL(T.SUM,0) AS '当月',IFNULL(T1.SUM,0) AS '上月',CONCAT( ROUND((IFNULL(T.SUM,0)-IFNULL(T1.SUM,0)) / IFNULL(T1.SUM,1) * 100, 2 ), '', '%' ) AS '同比增长/%',
IFNULL(T2.SUM,0) AS '上一年当前月份', 
CONCAT( ROUND((IFNULL(T.SUM,0)-IFNULL(T2.SUM,0)) / IFNULL(T2.SUM,1) * 100, 2 ), '', '%' ) AS '环比增长/%' 
FROM GRADE G 
LEFT JOIN
(SELECT COUNT(1) AS SUM,CLASS_NO AS CLASS_NO FROM STUDENT_INFO 
WHERE 
INPUT_TIME BETWEEN CONCAT(DATE_FORMAT(LAST_DAY(NOW()),'%Y-%m-'),'01') 
AND LAST_DAY(NOW()) 
GROUP BY CLASS_NO ASC LIMIT 12) 
AS T 
ON (G.CLASS_NO=T.CLASS_NO) 
LEFT JOIN 
(SELECT COUNT(1) AS SUM,CLASS_NO AS CLASS_NO FROM STUDENT_INFO 
WHERE 
INPUT_TIME BETWEEN CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),'%Y-%m-'),'01') 
AND LAST_DAY(NOW() - INTERVAL 1 MONTH)
GROUP BY CLASS_NO ASC LIMIT 12) 
AS T1 
ON (G.CLASS_NO=T1.CLASS_NO) 
LEFT JOIN 
(SELECT COUNT(1) AS SUM,CLASS_NO AS CLASS_NO FROM STUDENT_INFO 
WHERE 
INPUT_TIME BETWEEN CONCAT(DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 YEAR),'%Y-%m-'),'01') 
AND LAST_DAY(NOW() - INTERVAL 1 YEAR) 
GROUP BY CLASS_NO ASC LIMIT 12) 
AS T2 
ON (G.CLASS_NO=T2.CLASS_NO)