XML中MySQL语句和WHERE结合使用

最新推荐文章于 2023-12-02 15:22:18 发布

何小凡

最新推荐文章于 2023-12-02 15:22:18 发布

阅读量207

点赞数

分类专栏：笔记文章标签： mysql xml java

本文链接：https://blog.csdn.net/hexianfan/article/details/129736272

版权

笔记专栏收录该内容

81 篇文章 0 订阅

订阅专栏

该段内容是一个SQL查询，用于从tb_stamp表中选择数据并LEFTJOINview_seal_count和view_dept视图，根据多个条件进行过滤，如stamp_name,stamp_type,stamp_org等。查询还考虑了有效日期范围，并在where子句中处理了空值情况。

摘要由CSDN通过智能技术生成

<select id="selectTbStampList" parameterType="TbSeal" resultMap="TbStampResult">
    select sta.stamp_id, sta.stamp_name, sta.stamp_code,sta.picture_data,sta.picture_type,sta.stamp_type,sta.status,
    sta.valid_start, sta.valid_end, sta.stamp_org,sc.num,vd.dept_name,sta.sealModel_name,sta.sealModel_org from tb_stamp sta
    LEFT JOIN view_seal_count sc
    ON sta.stamp_id=sc.seal_id
    LEFT JOIN view_dept vd
    ON vd.dept_id=sta.organ_id
    <where>
        <if test="stampName != null  and stampName != ''">and sta.stamp_name like concat('%', #{stampName}, '%')
        </if>
        <if test="stampType != null  and stampType != ''">and sta.stamp_type = #{stampType}</if>
        <if test="stampOrg != null  and stampOrg != ''">and sta.stamp_org = #{stampOrg}</if>
        <if test="pictureData != null  and pictureData != ''">and sta.picture_data = #{pictureData}</if>
        <if test="pictureType != null  and pictureType != ''">and sta.picture_type = #{pictureType}</if>
        <if test="status != null  and status != ''">and sta.status = #{status}</if>
        <if test="organId != null  and organId != ''">and vd.dept_real_id = #{organId}</if>
        <if test="stampCode != null  and stampCode != ''">and sta.stamp_code = #{stampCode}</if>
        <if test="makeOrgCode != null  and makeOrgCode != ''">and sta.make_org_code = #{makeOrgCode}</if>
        <if test="params != null ">
            <if test="params.beginValidStart != null and params.beginValidStart != ''">
                <![CDATA[
                    and sta.valid_start >= CONCAT(#{params.beginValidStart},' 00:00:00')
                ]]>
            </if>
            <if test="params.endValidEnd != null and params.endValidEnd != ''">
                <![CDATA[
                  and sta.valid_end <= CONCAT(#{params.endValidEnd},' 23:59:59')
                ]]>
            </if>
        </if>
        AND status=1
    </where>
    ORDER BY sta.create_time DESC
</select>