mysql 最高分_MySQL获得最高平均排名

I am trying to write a mysql query to return the top 3 courses that have the highest average course rating. I have two tables, Ratings and Courses.

The Ratings table:

courseId rating

1 6

2 2

1 4

2 5

3 3

4 0

6 0

The Courses Table:

courseId cnum cname

1 100 name1

2 112 name2

3 230 name3

4 319 name4

5 122 name5

6 320 name6

I need to return the top 3 courses that have the highest average rating. Any ideas how I could do this? Thanks

解决方案SELECT Courses.*

FROM Courses NATURAL JOIN (

SELECT courseId, AVG(rating) avg_rating

FROM Ratings

GROUP BY courseId

ORDER BY avg_rating DESC

LIMIT 3

) t

See it on sqlfiddle.