题1

/practice/203d0aed8928429a8978185d9a03babc?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

解1

(select exam_id as tid,count(distinct uid) as uv,count(exam_id) as pvFROMexam_recordgroup byexam_id)union all # 不去重(select question_id as tid,count(distinct uid) as uv,count(question_id) as pvFROMpractice_recordgroup byquestion_id)order BYLEFT(tid, 1) desc,uv desc,pv DESC;

解2

select * from(select exam_id as tid,count(distinct uid) as uv,count(*) as pvfrom exam_recordgroup by exam_idorder by uv desc, pv desc) as examunionselect * from(select question_id as tid,count(distinct uid) as uv,count(*) as pvfrom practice_recordgroup by question_idorder by uv desc, pv desc) as practice # 注意子表必须有表名。

解3

用tid字段的左边第一个数来排序。

要注意的是关于UNION后的排序问题，要么在UNION之前分别单独排序（如上解法），要么在union之后再排序：

(SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv,COUNT(*) pv FROM exam_recordGROUP BY exam_id)UNION(SELECT question_id AS tid, COUNT(DISTINCT practice_record.uid) uv,COUNT(*) pv FROM practice_recordGROUP BY question_id)ORDER BY LEFT(tid,1) DESC, uv DESC, pv DESC;

题2

/practice/a126cea91d7045e399b8ecdcadfb326f?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select uid,'activity1' as activityfrom exam_recordwhere year(submit_time) = group by uidhaving min(score) >= 85 # 对于每组 要求最小得分不小于85，过滤union all # 将两个筛选合并。不去重select distinct uid,'activity2' as activityfrom exam_record e_r join examination_info e_ion e_r.exam_id = e_i.exam_idwhere year(e_r.submit_time) = and e_i.difficulty = 'hard'and e_r.score > 80and timestampdiff(minute, e_r.start_time, e_r.submit_time) * 2 < e_i.durationorder by uid

或：

select uid,'activity1' as activityfrom exam_recordwhere year(submit_time) = group by uidhaving min(score) >= 85 # 对于每组 要求最小得分不小于85，过滤union all # 将两个筛选合并。不去重select distinct uid,'activity2' as activityfrom exam_record e_r join examination_info e_ion e_r.exam_id = e_i.exam_idwhere year(submit_time) = and difficulty = 'hard'and score > 80and timestampdiff(minute, start_time, submit_time) * 2 < durationorder by uid