我怎样才能重温前3个评级事件MySQL

Question

I have sporting events that users can rate. All data is stored in a Mysql database.

Ratings_table:

rating_id PK 
organization float //subrating
value_for_money float //subrating
fun_factor float //subrating
facilities float //subrating
event_id int
user_id int

Event_table:

event_id PK
event_name varchar

Is there a way I can use Mysql query to join the tables and order them so that I can retrieve the 3 events with the highest average rating of the sub ratings?

Answer 1

I think this may be it:

SELECT e.event_id, event_name, avg_rating
FROM Event_table e
JOIN (
    SELECT event_id, MAX((organization+value_for_money+fun_factor+facilities)/4) avg_rating
    FROM Ratings_table
    GROUP BY event_id
    ORDER by avg_rating DESC
    LIMIT 3) r
ON e.event_id = r.event_id