数据库之union与order by同时使用
技术拓展
2022年5月14日
36
数据库之union与order by同时使用已关闭评论
0
导读:本篇文章讲解 数据库之union与order by同时使用,文章出自:https://blog.csdn.net/weixin_43316702/article/details/108094909希望对大家有帮助,欢迎收藏,转发!站点地址:www.javazhiyin.com.com
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
)