mybatis中常见的动态SQL标签

在xml中写动态SQL的的时候，有一些常见的，如if 、foreach

        SELECT a.*,c.product_name
        FROM work_order a LEFT JOIN product c ON a.product_code = c.product_code AND c.del_flag = 0
        where a.del_flag = 0 
        <if test="orderQueryReq.productCode != null and orderQueryReq.productCode != ''">
            AND a.product_code like concat('%',#{orderQueryReq.productCode},'%')
        </if>
        <if test="orderQueryReq.productName != null and orderQueryReq.productName != ''">
            AND c.product_Name like concat('%',#{orderQueryReq.productName},'%')
        </if>
        <if test="orderQueryReq.orderStatus != null">
            AND a.order_status = #{orderQueryReq.orderStatus}
        </if>
        <if test="orderQueryReq.auditStatus != null">
            AND a.audit_status = #{orderQueryReq.auditStatus}
        </if>
        <if test="orderQueryReq.startTimeStart != null and orderQueryReq.startTimeStart != ''">
            AND a.start_time <![CDATA[ >= ]]> #{orderQueryReq.startTimeStart}
        </if>
        <if test="orderQueryReq.startTimeEnd != null and orderQueryReq.startTimeEnd != ''">
            AND a.start_time <![CDATA[ <= ]]> #{orderQueryReq.startTimeEnd}
        </if>
        <if test="orderQueryReq.endTimeStart != null and orderQueryReq.endTimeStart != ''">
            AND a.end_time <![CDATA[ >= ]]> #{orderQueryReq.endTimeStart}
        </if>
        <if test="orderQueryReq.endTimeEnd != null and orderQueryReq.endTimeEnd != ''">
            AND a.end_time <![CDATA[ <= ]]> #{orderQueryReq.endTimeEnd}
        </if>
        <if test="orderQueryReq.orderIds != null and orderQueryReq.orderIds.size() > 0">
            AND a.id IN
            <foreach collection="orderQueryReq.orderIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        ORDER BY field(a.audit_status, 10,30,20) , field(a.order_status,30,20,10,40) ,a.start_time ASC

如上涉及到if标签内，如果为string的时候判断，Integer时候判断，时间字段的判断，遍历集合时候写法

order by如果不是按照某一个字段规律的顺序来的，可以使用field关键字，里面第一项填写的是属性字段，后面就按照想排序的值进行排序，也就不用填desc和asc