mysql 根据年，月，日，小时，分别查询对应时间的数量

给自己记录下

实现了选择年分布时，报表按照12个月来统计每个月的数量

选择月分布，报表按照月的天数来统计每天的数量

选择日分布，报表按照24小时来统计每小时的数量

年 ：

<select id="year" parameterType="map" resultMap="BaseResultMap">
		SELECT 
		  COUNT(*) AS count ,
		  DATE_FORMAT(r.entryTime, '%m') AS laber
		FROM
		  tab_fangke_visit_person r
		WHERE
			1=1
			AND DATE_FORMAT(r.entryTime, '%Y') = #{value}
			<if test="endtime != null and endtime.trim() != ''">
				AND r.outTime is not null  
			</if>
		GROUP BY DATE_FORMAT(r.entryTime, '%m')
</select>

月：

<select id="month" parameterType="map" resultMap="BaseResultMap">
		SELECT 
		  COUNT(*) AS count,
		  DATE_FORMAT(r.entryTime, '%d') AS laber
		FROM
		  tab_fangke_visit_person r 
		WHERE 
			1=1
			AND DATE_FORMAT(r.entryTime, '%Y-%m') = #{value}
			<if test="endtime != null and endtime.trim() != ''">
				AND r.outTime is not null  
			</if>
		GROUP BY DATE_FORMAT(r.entryTime, '%d')
	</select>

日 ：

<select id="day" parameterType="map" resultMap="BaseResultMap">
		SELECT 
		  HOUR(r.entryTime) AS laber,
		  COUNT(*) AS count
		FROM
		  tab_fangke_visit_person r 
		  WHERE 
			1=1
			AND DATE_FORMAT(r.entryTime, '%Y-%m-%d') = #{value}
			<if test="endtime != null and endtime.trim() != ''">
				AND r.outTime is not null  
			</if> 
		GROUP BY HOUR(r.entryTime) 

	</select>

 

根据年月得出月有多少天

// 获取某月有多少天
	public int getMonthLastDay(Date date) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		return calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
	}

补足缺失的日期

// 补足缺失的日期，并顺序排序。
	public List<CountValue> setValue(List<CountValue> arr, List<String> month) {
		for (String mon : month) {
			CountValue val = new CountValue(0, mon);
			if (!arr.contains(val)) {
				arr.add(val);
			}
		}
		Collections.sort(arr, new Comparator<CountValue>() {
			@Override
			public int compare(CountValue o1, CountValue o2) {
				return o1.getLaber().compareTo(o2.getLaber());
			}
		});
		return arr;
	}

CountValue类的equst方法：

public boolean equals(Object obj) {
		if (obj instanceof CountValue) {
			CountValue p = (CountValue) obj;
			String key = p.getLaber();
			if (null != key && key.equals(this.laber)) {
				return true;
			} else {
				return false;
			}
		}
		return false;
	}

前台datetimepicker控件的年，月，日配置：

function initYear(){
	$('#year').datetimepicker({  
	    startView: 'decade',  
	    minView: "decade",  
	    format: 'yyyy',  
	    maxViewMode: 2,  
	    minViewMode:2,  
	    autoclose: true,
	    language:  'zh-CN',
	}).on('changeDate',function(ev){
		var year = $('#year').val();
		init(year,"月份","月","year");
	});
}


function initMonth(){
	$('#month').datetimepicker({  
		startView: 'year',  
	    minView: "year",  
	    format: 'yyyy-mm',  
	    maxViewMode: 2,  
	    minViewMode:2,  
	    autoclose: true,
	    language:  'zh-CN',
	}).on('changeDate',function(ev){
		var year = $('#month').val();
		init(year,"日","","month");
	});
}

function initDay(){
	$('#day').datetimepicker({  
		minView: "month", //选择日期后，不会再跳转去选择时分秒 
	    language:  'zh-CN',
	    format: 'yyyy-mm-dd',
	    todayBtn:  1,
	    autoclose: 1,
	}).on('changeDate',function(ev){
		var year = $('#day').val();
		init(year,"小时","","day");
	});
}