问题

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