一个学生表(student) :学生姓名(sname),课程(scourse),分数(score),(学生和课程之间是多对多的关系,即一个学生可选多门课,一门课可以被多个学生选) 要求:查找选课大于3门,且平均分数最好的两个学生
sql语句:mysql> select avg(score) score,count(scourse) scourse,sname from student group by sname having scourse>3 order by score desc limit 0,2;(这个字段没有其别名select avg(score) score,count(scourse) scourse,sname from student group by sname having count(scourse)>3 order by avg(score) desc limit 0,2;)结果如下:
+---------+---------+-------+
| score | scourse | sname |
+---------+---------+-------+
| 30.0000 | 4 | yyx |
| 20.0000 | 4 | zmh |
+---------+---------+-------+
2 rows in set