今天练习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