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

问题描述：

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?

我有用户可以评分的体育赛事。所有数据都存储在Mysql数据库中。 p>

Ratings_table： p>

  rating_id PK 
organization float // subrating 
value_for_money float // subrating  
fun_factor float // subrating 
facilities float // subrating 
event_id int 
user_id int 
  code>  pre> 
 
  Event_table： p> 
 
 
  event_id PK 
event_name varchar 
  code>  pre> 
 
 有没有办法可以使用Mysql查询连接表并对它们进行排序，以便我可以检索最高的3个事件 子评级的平均评级？ p> 
  div>

答

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

答

Try this::

Select 
distinct r.eventId
from 
ratingTable r
inner join eventTable  e on (r.eventId=e.eventId)
order by rating_id limit 3

But it also can be achieved by

Select 
    distinct r.eventId
    from 
    ratingTable r
    inner join eventTable 
order by rating_id limit 3

答

This would be a very complex query to do it all in one single mysql query. Personally I would do this in PHP. This is an example of how you can do it in PHP and MySQLi

$ratings = array();
$query = "SELECT * FROM ratings";
$results = $dbConn->query($query);
//First we load all results into a big array.
while($row = $results->fetch_assoc($results) {
    if(!isset($ratings[$row['rating_id']]) {
        $ratings[$row['event_id']]['count'] = 0;
    }
    $ratings[$row['event_id']]['count']++;
    $ratings[$row['event_id']]['organisation'] += $row['organisation'];
    $ratings[$row['event_id']]['value_for_money'] += $row['value_for_money'];
    $ratings[$row['event_id']]['fun_factor'] += $row['fun_factor'];
    $ratings[$row['event_id']]['facilities'] += $row['facilities'];
}
//Now we go through each event and average out the results.
$averages = array();
foreach($ratings as $event_id => $data) {
    $averages[$event_id]['organisation'] = $data['organisation'] / $data['count'];
    $averages[$event_id]['value_for_money'] = $data['value_for_money'] / $data['count'];
    $averages[$event_id]['fun_factor'] = $data['fun_factor'] / $data['count'];
    $averages[$event_id]['facilities'] = $data['facilities'] / $data['count'];
}
//Now we can sort by whatever we want to:

Alternatively if you want the average across all 4 ratings you can use this bit on the end instead for the averages:

$averages = array();
foreach($ratings as $event_id => $data) {
    $averages[$event_id] = (($data['organisation'] + $data['value_for_money'] + $data['fun_factor'] + $data['facilities']) / 4) / $data['count'];
}

Please note: I have not tested this code... you should check it for bugs yourself.

For sorting, please check various stack overflow answers on sorting multidimensional arrays. Sort Multi-dimensional Array by Value or How do I Sort a Multidimensional Array in PHP

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

相关推荐