一、明确需求

 统计每个题目和每份试卷作答人数和次数

二、问题拆解

1. 分别计算每份试卷和每道题目的作答人数和次数，结果作为表1和表2
2. 对表1和表2 union 连接

三、代码实现

方法1：排序好后在union联结

select * 
from (
select exam_id,count(distinct uid) uv,count(uid) pv
from exam_record 
group by exam_id
order by uv desc,pv desc )x 
union 
select * 
from (
select question_id,count(distinct uid) uv,count(uid)pv
from practice_record
group by question_id
order by uv desc,pv desc )x 

• 注意：要求分别对试卷数的uv和pv降序排后在接题目数的uv和pv的降序排

方法2：先union后再指定exam_id的左数排序

select exam_id as tid,count(distinct uid) uv,count(uid) pv
from exam_record 
group by exam_id
union 
select question_id as tid,count(distinct uid) uv,count(uid)pv
from practice_record
group by question_id
order by left(tid,1)desc,uv desc,pv desc 

四、拓展

• 1、两个表之间的union只能有一个order by子句
• 2、若要两个表的记录都分别准从排序规则，先分别排序后再用一个select*外套后再联结（如本题解方法1）
• 3、自定义排序

order by left/right(col_name,num) desc/asc
按col_name的左/右边第num个数进行降序/升序排序