按最高平均评分排序 [英] Sort by highest average rating

问题描述

我有一个名为ranks的表，该表存储1-7值，并将其与item和user id关联.

I have a table called ranks which stores a 1-7 value and connects it with an item and user id.

像这样:id | userid | value | itemid.

要显示项目的等级，我计算所有值(1-7)的总和(itemid=?)，然后将其除以总行数.但是items表本身没有存储任何值.

To display an item's rating, I calculate the sum of all the values (1-7) where itemid=? and divide it by the total number of rows. But I have no value stored in the items table itself.

获取平均评分:

    // total

    $stmt = $conn->prepare("SELECT SUM(value) as total FROM ranks WHERE itemid=?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->bind_result($total);
    $stmt->fetch();
    $stmt->close();

    // num of rows

    $stmt = $conn->prepare("SELECT COUNT(value) FROM ranks WHERE itemid=?");
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $stmt->bind_result($count);
    $stmt->fetch();
    $stmt->close();

    $avg = $total/$count;

但是我不知道如何按照最高评分进行排序，除非我将评分存储在items表本身中.单独的ranks表可以吗?

But I have no idea how to sort by highest rated unless I have the rating stored in the items table itself. Is it possible with the separate ranks table?

推荐答案

您可以使用avg()来按其最高评分检索itemid.这与获取总和并除以计数相同:

You can use avg() to retrieve itemid's by their highest rating. This is the same as getting the sum and dividing by the count:

select avg(value), itemid
from ranks
group by itemid
order by avg(value) desc

这篇关于按最高平均评分排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！