今天練習SQL，使用union all 連接各個查詢導致我的各個查詢排序失效，最后發現使用union all后會忽略各個模塊的order by，只有最外層的order by才會生效

原SQL如下：

(
selectexam_id tid,count(distinct uid) uv, count(uid) pv
fromexam_record
group byexam_id
order byuv desc, pv desc
)union all
(
selectquestion_id tid,count(distinct uid) uv, count(uid) pv
frompractice_record
group byquestion_id
order byuv desc, pv desc
)

實際上里面的order by都被忽略了，解決方案如下：

方案① 嵌套一層select

select*
from
(selectexam_id tid,count(distinct uid) uv, count(uid) pvfromexam_recordgroup byexam_idorder byuv desc, pv desc
) tunion allselect*
from
(selectquestion_id tid,count(distinct uid) uv, count(uid) pvfrompractice_recordgroup byquestion_idorder byuv desc, pv desc
)tt

方案 ② 在各個模塊加上limit

(
selectexam_id tid,count(distinct uid) uv, count(uid) pv
fromexam_record
group byexam_id
order byuv desc, pv desc
limit 99999
) union all(
selectquestion_id tid,count(distinct uid) uv, count(uid) pv
frompractice_record
group byquestion_id
order byuv desc, pv desc
limit 99999
)

使用limit之后會優先將limit對應的查詢強制執行完畢，再進行union all