错题难题丨学到了强大的筛选方法再也不怕逻辑难
试卷发布当天作答人数和平均分
https://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499?tpId=240&tqId=2183023&ru=/exam/oj&qru=/ta/sql-advanced/question-ranking&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D240
这道题的逻辑实在是太难分解了,如果按照之前的老办法先筛选比较简单的条件再继续筛选其他条件,
分步越多越容易错,今天发现了筛选条件还能一步到位,实在是太棒了!代码如下:
其中重点在于selec中有*,select b.*,就把按条件筛选后的数据完整拉出成一张完整的表
这里b.*的意思是在筛选条件下,显出b即exam_record的完整数据
SELECT
b.*,
LEVEL,
tag,
DATE(release_time)
FROM user_info a
INNER JOIN exam_record b
on a.uid=b.uid
INNER JOIN examination_info c
on b.exam_id=c.exam_id
WHERE
level>5
AND
tag='sql'
AND
DATE(release_time)=DATE(start_time)
然后我们再按需要select出需要的数据,再排序就好了
SELECT
exam_id,
COUNT(DISTINCT uid)uv,
ROUND(AVG( score),1)avg_score
FROM(SELECT
b.*,
LEVEL,
tag,
DATE(release_time)
FROM user_info a
INNER JOIN exam_record b
on a.uid=b.uid
INNER JOIN examination_info c
on b.exam_id=c.exam_id
WHERE
level>5
AND
tag='sql'
AND
DATE(release_time)=DATE(start_time)
)q
GROUP BY exam_id
ORDER BY uv DESC,avg_score