題解 | #得分不小于平均分的最低分#
得分不小于平均分的最低分
http://www.fangfengwang8.cn/practice/3de23f1204694e74b7deef08922805b2
我認(rèn)為我在這道解題的方法用到的絕妙用處是1 flag
WITH t1 AS ( SELECT exam_id, score, 1 flag FROM exam_record WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag="SQL") AND score IS NOT NULL ), t2 AS ( SELECT AVG(score ) avg_sc, 1 flag FROM t1 ), t3 AS ( SELECT t1.score, t2.avg_sc FROM t1 JOIN t2 ON t1.flag = t2.flag WHERE t1.score-t2.avg_sc >= 0 ) SELECT MIN(score) min_score_over_avg FROM t3
