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

##试卷发布以后,当天：start_tima=release_time，level>5,uv=count(distinct(uid)),avg_score = sum(socre)/uv

## 嵌套子查询
SELECT exam_id, COUNT(DISTINCT(uid)) uv, ROUND(AVG(score),1) avg_score
FROM exam_record
WHERE (exam_id,DATE(start_time)) IN(
    SELECT exam_id,DATE(release_time)
    FROM examination_info where tag = 'SQL'
)AND  uid IN (
    SELECT uid from user_info where level >5 
)
GROUP BY exam_id
ORDER BY uv DESC, avg_score;


## 表的连接1
SELECT exam_record.exam_id, COUNT(DISTINCT(exam_record.uid)) uv, ROUND(AVG(exam_record.score),1) avg_score
FROM exam_record
LEFT JOIN examination_info
ON exam_record.exam_id = examination_info.exam_id
LEFT JOIN user_info
ON exam_record.uid = user_info.uid
WHERE examination_info.tag = 'SQL' and DATE(exam_record.submit_time) is not null and DATE(examination_info.release_time) = DATE(exam_record.start_time) and user_info.level > 5
GROUP BY exam_id
ORDER BY uv DESC, avg_score;


## 表的连接2
SELECT exam_record.exam_id, COUNT(DISTINCT(exam_record.uid)) uv, ROUND(AVG(exam_record.score),1) avg_score
FROM exam_record,examination_info,user_info
WHERE exam_record.exam_id = examination_info.exam_id AND exam_record.uid = user_info.uid
AND examination_info.tag = 'SQL' and DATE(exam_record.submit_time) is not null and DATE(examination_info.release_time) = DATE(exam_record.start_time) and user_info.level > 5
GROUP BY exam_id
ORDER BY uv DESC, avg_score;