我正在尝试获取rating
表中rate
列的平均评分。在查找了许多方法来做到这一点之后,似乎没有什么对我有用。但是,如果我不尝试取平均值,它确实有效。
这是我的代码(尝试 1):
$sql = "SELECT * FROM users
INNER JOIN mentor ON users.id = mentor.id
INNER JOIN (SELECT AVG(rating.rate) AS average
FROM rating GROUP BY rating.owner)
ON mentor.id = rating.owner ORDER BY RAND()";
这是我的代码(尝试 2):
$sql = "SELECT users.id, users.username,
mentor.industry, mentor.city,
AVG(rating.rate) AS average FROM users
INNER JOIN mentor ON users.id = mentor.id
INNER JOIN rating ON mentor.id = rating.owner
ORDER BY RAND()";
SELECT
语句后面的代码是这样的:
$res = mysqli_query($db,$sql) or die(mysqli_error());
if (mysqli_num_rows($res)>0) {
while ($row=mysqli_fetch_assoc($res)){
$variable= $row['column'];
// more variable naming
}
}
每当我重新加载我的页面时,它都是完全白色的。控制台日志显示"加载资源失败:服务器响应状态为 404(未找到)"。不太确定下一步该怎么做。感谢您的任何建议,建议!
owner
列为 = mentor.id = users.id
您有几个问题需要解决。问题是你得到一个404。尝试确保请求正确的页面。
第二个问题是你的SQL查询,这是完全错误的。您尝试获取记录数据并聚合而不group
,这会引发错误。第二次尝试更接近正确的解决方案,所以让我们从那里开始。这是尝试:
SELECT users.id, users.username,
mentor.industry, mentor.city,
AVG(rating.rate) AS average FROM users
INNER JOIN mentor ON users.id = mentor.id
INNER JOIN rating ON mentor.id = rating.owner
ORDER BY RAND()
尝试按原样在 RDBMS 中运行此查询。你会得到一个错误。您需要按用户group
结果,因此用户数据将变得汇总,并且由于导师正在给出评级,因此您无法select
导师,但您可以select
平均rating
,但随后group
users
:
SELECT users.id, users.username,
AVG(rating.rate) AS average FROM users
INNER JOIN mentor ON users.id = mentor.id
INNER JOIN rating ON mentor.id = rating.owner
group by users.id, users.username
ORDER BY RAND()