好吧,我又一次在努力制作我的电影分级系统:(
我的表结构是

      ID | UserID | Rating | TMDB |  TYPE
      -----------------------------------
       1      34        6     432      2
      -----------------------------------
       2      34        9     432      3
      -----------------------------------
       3      44        9     468      2

多亏了用户barmar,我才算出
$sql = "SELECT AVG(rating) AS avg_rating FROM `tbl_rating` WHERE `tmdb`= :tmdb AND `type`= :type ";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':tmdb', $tmdb);
$stmt->bindParam(':type', $type);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$avg_rating = $row['avg_rating'];

现在我想列一个五大恐怖事件(类型=2)。我完全不知道如何解决这个问题:(我知道我可以在最后通过一个简单的限制命令来限制结果,我认为需要按平均值(rating)排序?我需要删除where tmdb。我不知道如何做到这一点请大家帮忙!

最佳答案

您可以将查询修改为按tmdb(而不是按筛选条件)分组,按avg_rating(降序)对结果排序,并将返回的行限制为5。像这样的:

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= :type
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5

概念证明:
您可以在mysql控制台中运行以下sql命令来验证查询是否对示例数据返回合理的结果。
create table tbl_rating (
ID int unsigned primary key auto_increment,
UserID int unsigned,
Rating smallint unsigned,
tmdb int unsigned,
type int unsigned) engine=innodb;

insert into tbl_rating
(UserId, Rating, tmdb, `type`) values
(34, 6, 432, 2),
(34, 9, 432, 3),
(44, 9, 468, 2);

SELECT `tmdb`, AVG(rating) AS avg_rating
FROM `tbl_rating`
WHERE `type`= 2
GROUP BY `tmdb`
ORDER BY avg_rating DESC
LIMIT 5;

+------+------------+
| tmdb | avg_rating |
+------+------------+
|  468 |     9.0000 |
|  432 |     6.0000 |
+------+------------+
2 rows in set (0.01 sec)

关于php - pdo使用avg列出前5名?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/39516253/

10-11 03:42