题解 | #试卷发布当天作答人数和平均分#

法一：直观地把三个表连起来，把条件写上去。

SELECT examination_info.exam_id, COUNT(DISTINCT exam_record.uid) uv, 
ROUND(AVG(score),1) avg_score
FROM user_info, examination_info, exam_record
WHERE tag='SQL' and level>5 and DATE(release_time)=DATE(submit_time) and
user_info.uid=exam_record.uid and
examination_info.exam_id=exam_record.exam_id
GROUP BY examination_info.exam_id
ORDER BY uv DESC, avg_score ASC;

法二：后来想能不能不直接连三张表，用子查询做，节约空间什么的。思路是从exam_record表入手，exam_id可以加约束（用上examination_info表），uid可以加约束（用上user_info表）。但是有一个“试卷发布当天作答”条件，一定要把exam_record表和examination_info表连起来，因此就只能这样写了-_-

SELECT exam_record.exam_id, COUNT(DISTINCT uid) uv, ROUND(AVG(score),1) avg_score
FROM exam_record, examination_info
WHERE exam_record.exam_id IN (SELECT exam_id FROM examination_info WHERE tag= 'SQL')
AND 
uid IN (SELECT uid FROM user_info WHERE level>5)
AND 
exam_record.exam_id=examination_info.exam_id AND DATE(release_time)=DATE(submit_time)
GROUP BY exam_record.exam_id
ORDER BY uv DESC, avg_score ASC;