oracle mapper.xml sql查询

日期字符串转化为时间带时分秒 <select id="selectByModifiedDate" resultMap="BaseResultMap"> select * from clamc_operating_data_his <where> <if test="today!=null "> and MODIFIED_DATE <![CDATA[>= ]]> to_date(#{today},'yyyy-mm-dd HH24:mi:ss') </if> <if test="tomorrow!=null "> and MODIFIED_DATE <![CDATA[< ]]> to_date(#{tomorrow},'yyyy-mm-dd HH24:mi:ss') </if> </where> </select>

模糊查询 <select id="selectDataManaList" resultMap="BaseResultMap"> select * from clamc_operating_data_mana t <where> <if test="queryStr !=null and queryStr !='' "> t.FILE_DESCRIBE like '%'||#{queryStr}||'%' or FILE_NAME like '%'||#{queryStr}||'%' </if> </where> order by MODIFIED_DATE </select>

根据特定要求排序 select * from clamc_operating_etl_data t order by decode(t.execut_case_code, '1', 1, '2', 2, '3', 3, '0', 4, null, 99) asc

//sql分页查询 Select * from (select ROWNUM rn, t.* from (select * from clamc_operating_data_his)t) where rn between 11 and 20;

foreach <select id="selectHsConfHisByMatchineName" resultMap="BaseResultMap"> select MACHINE_NAME, FILE_NAME from clamc_operating_hs_conf_his t <where> <if test="sDate!=null"> business_date=#{sDate} </if> <if test="list!=null"> and MACHINE_NAME in <foreach collection="list" item="matchName" open="(" close=")" separator=","> #{matchName} </foreach> </if> </where> </select>

取最大日期 <select id="selecRobotLogByMatchineName" parameterType="String" resultMap="BaseResultMap"> select * from trustdis_logs t1 <where> t1.creat_date in (select max(creat_date) from trustdis_logs t2

        <where>
            <if test="today!=null  ">
                and t2.creat_DATE <![CDATA[>= ]]> to_date(#{today},'yyyy-mm-dd HH24:mi:ss')
            </if>
            <if test="tomorrow!=null ">
                and t2.creat_DATE <![CDATA[< ]]> to_date(#{tomorrow},'yyyy-mm-dd HH24:mi:ss')
            </if>

            <if test="list!=null">
                and t2.task_name in
                <foreach collection="list" item="machineName" separator="," open="(" close=")">
                    #{machineName}
                </foreach>

            </if>
            group by task_name)

        </where>

        <if test="list!=null">
            and t1.task_name in
            <foreach collection="list" item="machineName" separator="," open="(" close=")">
                #{machineName}
            </foreach>

        </if>

    </where>

</select>

转载于:https://my.oschina.net/u/3668344/blog/2988384