mybatis.xml根据时间查询最接近该时间数据遇到查询数据为空问题

最新推荐文章于 2023-09-15 09:57:35 发布

m0_57895351

最新推荐文章于 2023-09-15 09:57:35 发布

阅读量463

点赞数 1

分类专栏：工作总结文章标签： xml sql java

本文链接：https://blog.csdn.net/m0_57895351/article/details/120850936

版权

工作总结专栏收录该内容

4 篇文章 0 订阅

订阅专栏

    <select id="selectLatelyData" resultType="com.hrsoft.model.WaterLevelEntity" >
        SELECT *,ABS(TIMESTAMPDIFF(SECOND,<![CDATA[ CONCAT(#{paramDate},'00:00:00' )]]>,datetime)) as diff
        FROM water_level
        WHERE 1=1
            <![CDATA[  and datetime <= CONCAT(#{addDate},'00:00:00' )]]>
            <![CDATA[  and datetime >= CONCAT(#{minusDate},'00:00:00' )]]>
    </select>
</mapper>
使用下面函数转换

<![CDATA[ and datetime <= CONCAT(#{addDate},'00:00:00' )]]>

  sql.append(" and a.registerTime >= convert(datetime,?) and a.registerTime <= convert(datetime,?) ");

查询不到数据是因为我我的传参是String类型而数据库是dateTime类型所有要用到上面函数进行转换

不同类型转char

  SELECT CAST(dt_beta1 AS CHAR) beta1,
               CAST(dt_beta2 AS CHAR) beta2,
               CAST(dt_h11 AS CHAR)   h11,
               CAST(dt_h12 AS CHAR)   h12,
               CAST(dt_h21 as CHAR)   h21,
               CAST(dt_h22 as CHAR)   h22,
               CAST(dt_s1 as CHAR)    hrs1,
               CAST(dt_s2 as CHAR)    hrs2,
               CAST(dt_miu11 as CHAR) miu11,
               CAST(dt_miu12 as CHAR) miu12,
               CAST(dt_miu21 as CHAR) miu21,
               CAST(dt_miu22 as CHAR) miu22
--                CAST(dt_s3 as CHAR)    hrs3,
--                CAST(dt_h31 as CHAR)   h31,
--                CAST(dt_h32 as CHAR)   h32,
--                CAST(dt_miu3 as CHAR) beta3
        FROM t_dt
        WHERE dt_name = 'd'
          AND dt_default = (CASE
                                WHEN (SELECT COUNT(*) FROM t_dt WHERE dt_name = 'd') > 1 THEN 0
                                ELSE
                                    1
            END
            )