mybatis分页多条件查询指定时间段数据的sql语句

痛定思痛。 2024-02-18 16:02 51阅读 0赞

### 本案例适用于mysql数据库，在数据库中时间数据类型为datetime。   使用以下sql语句将不会报错，但也得不到想要的结果;   <select id="getGuidesList" parameterType="map" resultMap="GuideResultMap" flushCache="true">   SELECT   a.guide\_id,a.pat\_id,a.direct\_reason,a.sport\_direct,a.food\_direct,a.drug\_suggest,a.guide\_time,a.memo,b.name,c.doctorName   from (pc\_healthguide a LEFT JOIN pc\_patient b on a.pat\_id=b.pat\_id) LEFT JOIN pc\_doctor c on a.doctor\_id=c.doc\_id   <where>   <if test="doctorId!=null and doctorId !='0'">   a.doctor\_id=\#\{doctorId\}   </if>   <if test="name != null and name!= ''">   and name like concat ('%',\#\{name\},'%')   </if>   <if test="directReason!=null and directReason !=''">   and direct\_reason=\#\{directReason\}   </if>   <if test="guide\_time!=null and guide\_time!=''">   and guide\_timebetween \#\{beginTime\} and \#\{endTime\}   </if>   </where>   <if test="start!=null and size!=null">   limit \#\{start\},\#\{size\}   </if>   </select>   项目运行后，select的sql语句where并没有拼接and createTime between \#\{beginTime\} and \#\{endTime\}这一串，正确用法这样才对：   <if test="directReason!=null and directReason !=''">   and direct\_reason=\#\{directReason\}   </if>   <if test="beginDate!=null and beginDate!=''">   <!\[CDATA\[ and DATE\_FORMAT(guide\_time, '%Y-%m-%d %H:%i:%s')>= DATE\_FORMAT(\#\{beginDate\}, '%Y-%m-%d %H:%i:%s') \]\]>   </if>   <if test="endDate!=null and endDate!=''">   <!\[CDATA\[ and DATE\_FORMAT(guide\_time, '%Y-%m-%d %H:%i:%s') <= DATE\_FORMAT(\#\{endDate\}, '%Y-%m-%d %H:%i:%s') \]\]>   </if>   其中：beginDate和endDate为实体类的属性，guide\_time为数据库中的字段名，不要搞混淆了。 ###