题解 | SQL23 #每个题目和每份试卷被作答的人数和次数#

我的稀碎答案

错因分析

select exam_id as tid,count(distinct uid) as uv,
count(uid) as pv
from exam_record
group by tid
order by uv desc,pv desc %%%哎呀呀
union all
select question_id as tid,count(distinct uid) as uv,
count(uid) as pv
from practice_record
group by tid
order by uv desc,pv desc %%%哎呀呀

易错点：union all后的排序问题仅可用一次order by

涉及知识点：

• union会过滤掉两个结果集中重复的行，而union all不会过滤掉重复行；
• 解决两个Order By问题的方法——分别括住、重命名、全选，再union all （只括住而不进行重命名与全选是不管用的，会使得部分order by命令失效）

提交答案

select * from
(select exam_id as tid,count(distinct uid) as uv,
count(uid) as pv
from exam_record
group by tid
order by uv desc,pv desc)t1
union all
select * from
(select question_id as tid,count(distinct uid) as uv,
count(uid) as pv
from practice_record
group by tid
order by uv desc,pv desc)t2