问题
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