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

# union合并
select exam_id AS tid, COUNT(distinct uid) AS uv,
COUNT(start_time) AS pv
FROM exam_record
GROUP BY exam_id

union
select question_id AS tid, COUNT(distinct uid) AS uv,
COUNT(submit_time) AS pv
FROM practice_record
GROUP BY question_id
ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC

对union 后的排序问题，ORDER BY 只能在最后面使用一次

方法一：观察可得，exam_id是以9开头的，question_id 是8开头的，可以对最左边的数进行排序

方法二：在UNION之前分别单独排序，但是要对查询结果进行命名

SELECT * FROM
( SELECT * FROM t1  ORDER BY 字段 ) newt1 ## 一定要对表重新命名，否则报错 
UNION
SELECT * FROM
( SELECT * FROM t2  ORDER BY 字段 ) newt2

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;