在mysql应用中,以范围进行查询的很多是以时间范围作为条件进行范围查询的,时间范围查询有
很多种写法,首先总结一下between…and…的使用方法:
<select id="conCurrentCount" parameterType="map" resultType="int">
SELECT COUNT(*) FROM tbl_report_info
<where>
ActionTime BETWEEN #{startTime} AND #{endTime}
</where>
</select>
讲解:
SELECT * FROM tbl_student_info WHERE `height` between 1113 and 1122
等同于:
SELECT * FROM tbl_student_info WHERE `height` >= 1113 and `height` <= 1122
SELECT * FROM tbl_student_info WHERE `createDate` between '20170101020304' and '20180101020304'
等同于:
SELECT * FROM tbl_student_info WHERE `createDate` >= '20170101020304' and `createDate` <= '20180101020304'
另外一种时间范围查询的方法如下:分别设定开始时间和结束时间:
<if test="endTime!=null and !"".equals(endTime.trim())">
AND endTime < #{endTime}
</if>
<if test="startTime!=null and !"".equals(startTime.trim())">
AND startTime > #{startTime}
</if>