Mybatis按时间范围查询表的数据的xml语句

目录

一、查询一天内的数据

二、查询一周内的数据

三、查询当年每个月的数据

 

---

刚学习Mybatis的朋友们可能遇到按时间范围查询数据表的需求，现在我把这些常用的三段查询语句分享给大家！

一、查询一天内的数据

    <select id="queryOneDay"  resultMap="resultMap">
        SELECT * FROM  表名
        WHERE  TO_DAYS(时间字段) = TO_DAYS(NOW())
    </select>

二、查询一周内的数据

    <select id="queryOneWeek"  resultMap="resultMap">
        SELECT * FROM 表名
        WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[ <= ]]> date(表字段)
    </select>

三、查询当年每个月的数据

注意要在 test 里面判断 month 参数不为空！！

       <if test="month != null and month==1">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-01')
		</if>
		<if test="month != null and month==2">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-02')
		</if>
		<if test="month != null and month==3">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-03')
		</if>
		<if test="month != null and month==4">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-04')
		</if>
		<if test="month != null and month==5">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-05')
		</if>
		<if test="month != null and month==6">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-06')
		</if>
		<if test="month != null and month==7">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-07')
		</if>
		<if test="month != null and month==8">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-08')
		</if>
		<if test="month != null and month==9">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-09')
		</if>
		<if test="month != null and month==10">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-10')
		</if>
		<if test="month != null and month==11">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-11')
		</if>
		<if test="month != null and month==12">
			AND DATE_FORMAT( 字段名,'%Y-%m') = CONCAT(YEAR(NOW()), '-12')
		</if>