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

#表明union相当于复制黏贴 顺序都是和原来的一样的 但是一旦重p排会打乱原有的顺序
#因此需要在union之前拍好顺序，可以通过每个子表多套一层select的方法避免整个表重排
#和解决在语句中间无法使用order by的问题
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 tid, COUNT(DISTINCT practice_record.uid) uv,COUNT(*) pv
FROM practice_record
GROUP BY question_id
ORDER BY uv DESC, pv DESC)T2

#.这个是通过标记重排分类试卷类型
select exam_id,uv,pv
from
(
select exam_id, count(distinct uid)as uv,count(*)as pv,'2' as test
from exam_record
group by exam_id
union
(
select question_id,count(distinct uid)as uv,count(*)as pv,'1' as test
from practice_record
group by question_id
)
) s
order by test desc,uv desc,pv desc