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

select t.tid ,
count(distinct uid) as uv,
count(*)
from (
select uid,exam_id as tid from exam_record
union 
select uid,question_id from practice_record)t
group by t.tid 

由于这道题要求对 试卷和练习的结果分别排序，所以上方的写法就没有办法实现了。

with t1 as 

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

select * from t1 
union 
select * from t2

由于union 的排序最能跟在最后的语句中，即只出现一次。

所以用临时表的方式，或者在每个union 外面再加一层查询都是可以的。

这题的有点刁钻了。