MyBatis怎么同时查询不同时间段的数据

小刘
  • 526

我想同时查询三个个时间段的数据

时间2021-05-16 车牌号京OFV501FV606 的数据
时间2021-05-12 车牌号京OFV501FV606 的数据
时间2021-05-10 车牌号京OFV501FV606 的数据

我现在的查询语句

SELECT bi.license_number, bi.begin_time, bi.entry_time FROM bulldozer_info bi
where bi.license_number="京OFV501FV606" and date(begin_time)='2021-05-16'

怎么将这三个时间段的数据一起查出来?

根据楼下的回答自己实现了

<select
            id="searchRefuseTreatmentPlantV1"
            resultType="com.bulldozer.entity.RefuseTreatmentPlant"
    >
        SELECT <include refid="Base_Column_List" />
        FROM refuse_treatment_plant
        <where>
            <!--添加if条件如果参数为空则不拼接sql-->
            <if test=" flagDelete!= null and flagDelete!= '' and flagDelete == 0">
                AND flag_delete = #{flagDelete}
            </if>
            <if test=" countType!= null and countType!= '' and countType == 'greater'">
                AND leave_count>entry_count
            </if>
            <if test=" countType!= null and countType!= '' and countType == 'less'">
                AND leave_count<![CDATA[ < ]]>entry_count
            </if>
            <if test=" licenseNumber != null and licenseNumber != '' ">
                AND license_number like concat('%',#{licenseNumber},'%')
            </if>
            <if test=" licenseNumber != null and licenseNumberType != null and
            licenseNumber != '' and licenseNumberType == 'equal'">
                AND license_number = #{licenseNumber}
            </if>
            <if test=" dateType != null and dateType== 'curDay'">
                AND to_days(warning_time) = to_days(NOW())
            </if>
            <if test=" timeStart != null and timeStart != '' and timeEnd != null and timeEnd != ''">
                AND DATE_FORMAT(entry_time,'%Y%m%d') BETWEEN #{timeStart} and #{timeEnd}
            </if>
            <if test=" timeStart != null and timeStart != '' ">
                <foreach collection="timeList" index="index" item="timeItem" open="AND (" close=")" separator="or" >
                    date(entry_time) = #{timeItem}
                </foreach>
            </if>

            <if test="startDateStr!=endDateStr">
                <if test="query.startDateStr != null and query.startDateStr != '' ">
                    and log_operate_time &gt;  DATE_FORMAT( #{query.startDateStr}, '%Y-%m-%d 00:00:00' )
                </if>
                <if test="query.endDateStr != null and query.endDateStr != '' ">
                    and log_operate_time &lt; DATE_FORMAT( #{query.endDateStr}, '%Y-%m-%d 23:59:59' )
                </if>
            </if>

        </where>

        <choose>
            <when test="sort=='ASC'">
                ORDER BY entryTime ASC limit #{start}, #{dataLength}
            </when>
            <otherwise>
                ORDER BY entryTime DESC limit #{start}, #{dataLength}
            </otherwise>
        </choose>
    </select>
回复
阅读 891
2 个回答

不知道我理解错你的意思没,我觉得几个or就行了吧
如果是这个意思的话, 可以在xml里面用循环标签

SELECT bi.license_number, bi.begin_time, bi.entry_time FROM bulldozer_info bi
where bi.license_number="京OFV501FV606" and 
(
date(begin_time)='2021-05-16'
or
date(begin_time)='2021-05-12'
or
date(begin_time)='2021-05-10'
)
zensezz
  • 2
新手上路,请多包涵
SELECT bi.license_number, bi.begin_time, bi.entry_time FROM bulldozer_info bi
where bi.license_number="京OFV501FV606" and 
(
date(begin_time) <='2021-05-16'
and 
date(begin_time)>='2021-05-10'
)

上面的2021-05-16 这种日期可以传参,大于等于,小于等于替换成xml对应的语句

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

宣传栏