数据库之union与order by同时使用

导读:本篇文章讲解 数据库之union与order by同时使用,文章出自:https://blog.csdn.net/weixin_43316702/article/details/108094909希望对大家有帮助,欢迎收藏,转发!站点地址:www.javazhiyin.com.com

>>强大,10k+点赞的 SpringBoot 后台管理系统竟然出了详细教程!

union是用于拼接的关键字,order by是用于进行排序的关键字,但同时使用时会发现order by的排序并没有效果,但单独执行拼接的一条语句时就会发现结果是正常的。
例子:
原sql语句:

select
        UUID              ,
        APP_LINK          ,
        APPROVER          ,
        OPER_TIME         ,
        FILE_ID as buyfile,
        OPER_ID           ,
        MAINFORMID
from
        MT_BUY_PRO_DOC
where
        mainformid = 'bb9cbf00-e4df-48b9-8fe5-33f23e116044'
union all
 (
                select
                        mbsi.uuid            ,
                        '确认开标情况与录入开标结果'      ,
                        user_name            ,
                        time                 ,
                        means_file as buyfile,
                        '0'                  ,
                        mbsar.mainformid
                from
                        mt_buy_supplier_input mbsi
                left join MT_BUY_SUPPLIER_AUDIT_RECORD mbsar
                on
                        mbsar.mainformid = mbsi.mainformid
                where
                        mbsar.mainformid = 'dc668d53-cf28-4e47-9b63-70902ed84390'
                    and means_file is not null
                    and means_file != ''
                order by
                        time desc limit 1
        )

解决:(增加查询,在排序完后再查出来拼接)

select
        UUID              ,
        APP_LINK          ,
        APPROVER          ,
        OPER_TIME         ,
        FILE_ID as buyfile,
        OPER_ID           ,
        MAINFORMID
from
        MT_BUY_PRO_DOC
where
        mainformid = 'bb9cbf00-e4df-48b9-8fe5-33f23e116044'
union all
SELECT
        *
FROM       (
                select
                        mbsi.uuid            ,
                        '确认开标情况与录入开标结果'      ,
                        user_name            ,
                        time                 ,
                        means_file as buyfile,
                        '0'                  ,
                        mbsar.mainformid
                from
                        mt_buy_supplier_input mbsi
                left join MT_BUY_SUPPLIER_AUDIT_RECORD mbsar
                on
                        mbsar.mainformid = mbsi.mainformid
                where
                        mbsar.mainformid = 'dc668d53-cf28-4e47-9b63-70902ed84390'
                    and means_file is not null
                    and means_file != ''
                order by
                        time desc limit 1
        )