题解 | #得分不小于平均分的最低分#
得分不小于平均分的最低分
http://www.nowcoder.com/practice/3de23f1204694e74b7deef08922805b2
SELECT MIN(score) min_score_over_avg FROM (SELECT er.exam_id ,tag,score , AVG(score) OVER (PARTITION BY tag) a FROM exam_record er RIGHT JOIN examination_info ei ON er.exam_id = ei.exam_id WHERE score IS NOT NULL AND tag= 'SQL' ) b WHERE score >=a
;
SELECT MIN(score)
FROM exam_record er
JOIN examination_info ei
ON er.exam_id= ei.exam_id
WHERE tag ='SQl'
AND score IS NOT NULL
AND score >=
(SELECT AVG(score) FROM exam_record er
JOIN examination_info ei
ON er.exam_id= ei.exam_id
WHERE tag ='SQl'
AND score IS NOT NULL)
