Oracle中UNION和ORDER BY共用方法

问题

SQL语句中，UNION拼接两个单独的SQL时候，单独的SQL中加入ORDER BY会报错，ORDER BY只能放在句末。

// 会报错的语句
SELECT S.S_ID AS ID,S.S_NAME AS NAME
FROM STUDENT S
ORDER BY S_NAME
UNION
SELECT S2.S_ID AS ID,S2.S_NAME AS NAME
FROM STUDENT2 S2
ORDER BY S_NAME

// 可以正常执行的语句
SELECT S.S_ID AS ID,S.S_NAME AS NAME
FROM STUDENT S
UNION
SELECT S2.S_ID AS ID,S2.S_NAME AS NAME
FROM STUDENT2 S2
ORDER BY S_NAME

但是要根据每条分语句的字段来排序的话，这样写是不行的。

解决方法

解决方法有两种

一、将结果集当做一个临时表再进行排序

SELECT T.* FROM (
SELECT S.S_ID AS ID,S.S_NAME AS NAME
FROM STUDENT S
UNION
SELECT S2.S_ID AS ID,S2.S_NAME AS NAME
FROM STUDENT2 S2) T
ORDER BY T.NAME

二、单独对表进行排序后再进行合并

SELECT T.* FROM (
SELECT S.S_ID AS ID,S.S_NAME AS NAME
FROM STUDENT S ORDER BY T.NAME) T
UNION
SELECT T2.* FROM (
SELECT S2.S_ID AS ID,S2.S_NAME AS NAME
FROM STUDENT2 S2 ORDER BY T.NAME) T2
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容