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