sql语句查询，面试的时候问的

Question

学生表 id 性别 分数
班级表 id 班级
学生班级表 学生id 班级id
有一个字段忘了

查询A班分数大于80的男生，并按高到低排序
查询A班的平均成绩

求解答

Answer 1

select a.* from `学生表` a left join `学生班级表` b on a.id = b.`学生id` left join `班级表` c on b.`班级id` = c.id where c.`班级` = 'A' and a.`分数` > 80 and a.`性别` = '男‘ order by a.score desc;

select avgs as score from (select avg(score) as avgs, c.`班级` as cl from `学生表` a left join `学生班级表` b on a.id = b.`学生id` left join `班级表` c on b.`班级id` = c.id group by c.`班级`) tmp where cl = 'A' ;