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

1. 使用Union子句 得到 的结果是最后使用Order by 排序的； 然后left(tid,1) 指利用tid 的第一个数字降序排列。


select exam_id tid,
       count(distinct uid) uv,
       count(start_time) pv
  from exam_record ER 
  group by exam_id 
union  
select question_id tid ,
       count(distinct uid) uv,
       count(submit_time) pv  
  from practice_record PR 
 group by question_id 
 order by left(tid,1) desc,uv desc,pv desc ; 

/*
 SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,
COUNT(start_time) pv FROM exam_record 
GROUP BY exam_id

UNION 

SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,
COUNT(submit_time) pv FROM practice_record 
GROUP BY question_id
 
ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC;
*/