mysql查询一年内每月的（某个字段值出现）天数

1、需求，查询一年内每月的（某个字段值出现）天数

分析：
1）、查询月份占位，如果不占位，没有记录的月份就不会显示，这是不行的（上图中没有记录的月份值为0，但是也要显示），代码如下图；

	SELECT DATE_FORMAT(date_sub(curdate(), interval 6 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 5 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 5 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 4 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 4 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 3 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 3 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 2 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 2 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 1 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(date_sub(curdate(), interval 1 month),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 0 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
	union all
	SELECT DATE_FORMAT(curdate(),"%m") as cDate,
	(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval -1 month),"%Y.%m.%d")and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue

2）、根据月份分组，代码如下图：

	group by FROM_UNIXTIME(ctime/1000, '%m')

3）、注意的是，每个月（某个字段值在某天出现的记录可能会重复）记录要去重，所以要根据“日”去重，因为一天不管出现多少次记录，也只需要显示一次就行，代码如下图：

	select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data

2、整体SQL语句如下

	select * from (
		select a.cDate,IFNULL(a.cValue,0) as cValue from (
		SELECT DATE_FORMAT(date_sub(curdate(), interval 6 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 5 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(date_sub(curdate(), interval 5 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 4 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(date_sub(curdate(), interval 4 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 3 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(date_sub(curdate(), interval 3 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 2 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(date_sub(curdate(), interval 2 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 1 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(date_sub(curdate(), interval 1 month),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval 0 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		union all
		SELECT DATE_FORMAT(curdate(),"%m") as cDate,
		(select count(1) from (select DISTINCT FROM_UNIXTIME(ctime/1000, '%Y.%m.%d') from device_facility_data where FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") < DATE_FORMAT(date_sub(curdate(), interval -1 month),"%Y.%m.%d") and user_id = 30 and data_type in ('mattr_invalid','mattr_valid')) as cValue ) as cValue
		) as a left join (
		select FROM_UNIXTIME(ctime/1000, "%Y.%m.%d") cDate,'' as cValue from device_facility_data
		where user_id = 30 and data_type in ('mattr_invalid','mattr_valid')
		group by FROM_UNIXTIME(ctime/1000, '%m')
		) b on a.cDate = b.cDate
	) as aa order by aa.cDate asc