多个表使用union时，又进行order by 排序会出数据顺序的位置错乱

所以需要在第二张表开始，对最后排序的字段进行起别名如oper_time 改成oper_time1等

这是原来的

    select * from (
    select t.batch_no, t.oper_name, t.oper_id, t.oper_time,'病历送出' operate_type
    from rec_mr_batch_no t
    <where>

        and t.batch_type = '送出批次'
        <if test="request.patientId != null ">
            AND t.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
           AND t.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>

    </where>
    union
    select null as batch_no,
    p.operator_name oper_name,
    p.operator_id   oper_id,
    p.operate_time  oper_time,
    p.operate_type
    from rec_paper_trace_log p
    <where>
        <if test="request.patientId != null ">
            AND p.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
            AND p.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>
        and p.operate_type ='取消送出'
    </where>
    union
    select null as batch_no,
    p.operator_name oper_name,
    p.operator_id   oper_id,
    p.operate_time  oper_time,
    p.operate_type
    from rec_paper_trace_log p
    <where>
        <if test="request.patientId != null ">
            AND p.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
            AND p.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>
        and p.operate_type ='退回送出'
    </where>
    )temp
    order by temp.oper_time

修改后查询正常显示,数据不会错乱

    select * from (
    select t.batch_no, t.oper_name, t.oper_id, t.oper_time,'病历送出' operate_type
    from rec_mr_batch_no t
    <where>

        and t.batch_type = '送出批次'
        <if test="request.patientId != null ">
            AND t.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
           AND t.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>

    </where>
    union
    select null as batch_no,
    p.operator_name oper_name,
    p.operator_id   oper_id,
    p.operate_time  oper_time1,
    p.operate_type
    from rec_paper_trace_log p
    <where>
        <if test="request.patientId != null ">
            AND p.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
            AND p.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>
        and p.operate_type ='取消送出'
    </where>
    union
    select null as batch_no,
    p.operator_name oper_name,
    p.operator_id   oper_id,
    p.operate_time  oper_time2,
    p.operate_type
    from rec_paper_trace_log p
    <where>
        <if test="request.patientId != null ">
            AND p.patient_id =  #{request.patientId, jdbcType=VARCHAR}
        </if>
        <if test="request.visitNo != null ">
            AND p.visit_no =  #{request.visitNo, jdbcType=VARCHAR}
        </if>
        and p.operate_type ='退回送出'
    </where>
    )temp
    order by temp.oper_time