Mysql 统计月份，年份

假设业务表是A,面积字段：mj，时间字段：gmt_create，要统计面积字段。

1. 统计近半年(近6个月)的数据

• 日期表sql：有点像临时表

select tmp.year_month from (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month`
) tmp

• 结合业务实现的sql实现如下

SELECT
	tmp.months,
IF (isnull(bs.mj),0,bs.mj) AS mj
FROM
	(
	SELECT
		DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS months UNION
	SELECT
		DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `year_month` UNION
	SELECT
		DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `year_month` UNION
	SELECT
		DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `year_month` UNION
	SELECT
		DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `year_month` UNION
	SELECT
		DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `year_month` 
	) tmp
	LEFT JOIN (
	-- 按时间分组且统计数据
			SELECT IF (ISNULL(sum(a.mj)),0,sum(a.mj)) AS mj,DATE_FORMAT(a.gmt_create,'%Y-%m') AS months FROM a GROUP BY mj,months
	) AS bs ON tmp.months = bs.months
WHERE
	1 =1 GROUP BY months,mj

2.统计近一年每个月份的数据

• 日期表的sql

select tmp.year_month from (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `year_month`
UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `year_month`
) tmp

• 结合业务表的sql,公式大致如下：

select 日期表.time, 业务表mj 
from 日期表 
left join 业务表 on 日期表.time = 业务表.time

上面的业务表是已经把主要的统计逻辑完成的，即统计数据且按时间分过组的。日期表只是为了补全某个日期内没有数据会跳过统计的问题。例如，本来01、02、03、04、05、06这几个月份，但04和05没有数据，则查出来的是01、02、03、06。

3.下个月

SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m');

4、近几年的年份

SELECT
   year(DATE_SUB( now(), INTERVAL 6 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 5 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 4 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 3 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 2 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 1 YEAR )) AS tmp_date UNION ALL
SELECT
   year(DATE_SUB( now(), INTERVAL 0 YEAR )) AS tmp_date

案例

SELECT a.tmp_date,IFNULL(tmp.count,0) AS countFROM (
SELECT YEAR (DATE_SUB(now(),INTERVAL 5 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 4 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 3 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 2 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 1 YEAR)) AS tmp_date UNION ALL
SELECT YEAR (DATE_SUB(now(),INTERVAL 0 YEAR)) AS tmp_date) a LEFT JOIN (
SELECT YEAR,sum(count) countFROM (
SELECT 'pd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_pdda GROUP BY DATE_FORMAT(create_time,'%Y') UNION ALL
SELECT 'zd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_zdda GROUP BY DATE_FORMAT(create_time,'%Y') UNION ALL
SELECT 'gd' type,count(*) count,DATE_FORMAT(create_time,'%Y') YEAR FROM bs_gdda GROUP BY DATE_FORMAT(create_time,'%Y')) AS a GROUP BY YEAR) AS tmp ON a.tmp_date=tmp.YEAR

近六年每年的数据