题解 | #UNION 排序使用#

看了别人的代码才知道: union 链接这块 需要使用SLECET * ()嵌套后才能生效.开始单纯以为整体嵌套就OK了，后面发现排序依旧没有生效，需要分别对union　的前后分别使用然后再连接，因此是不是可以说明这样一个问题，ORDER BY是表形成后进行的展示排序而真实的表其实一直都是排序前SELECT的那张表，因此需要嵌套一个SELCET　* ()重新取表确定顺序;

# 自己做出来的提交老是通过不了
# SELECT * FROM 
# (
#     (SELECT exam_id as tid,count(distinct uid) as uv, count(1) as pv
#     FROM exam_record
#     GROUP BY exam_id
#     ORDER BY uv DESC, pv DESC)
# UNION ALL
#     (SELECT question_id as tid,count(distinct uid) as uv, count(1) as pv
#     FROM practice_record
#     GROUP BY question_id
#     ORDER BY uv DESC, pv DESC)
# )b ;

查看后别人的:

SELECT * FROM
(SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,
COUNT(*) pv FROM exam_record
GROUP BY exam_id
ORDER BY uv DESC, pv DESC) t1
 
UNION
 
SELECT * FROM
(SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,
COUNT(*) pv FROM practice_record
GROUP BY question_id
ORDER BY uv DESC, pv DESC) t2;

方法二: LEFT(tid,1):用tid字段的左边第一个数来排序

SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,
COUNT(*) pv FROM exam_record 
GROUP BY exam_id

UNION 

SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,
COUNT(*) pv FROM practice_record 
GROUP BY question_id
 
ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC;