我使用下面的查询来计算和显示用户在一些测验中的平均分数
$query = 'SELECT AVG(answer) FROM quiz_answers WHERE id_user = '.$id_user.'';
$averagescore = mysql_query($query, $conn) or die(mysql_error());
$row = mysql_fetch_array($averagescore);
echo number_format($row['AVG(answer)'],3);
这部分工作得很好，但在这个个人信息旁边，我还想显示我们所有网站用户的前3名平均分数，就像一个小名人堂…
我尝试了以下代码:
$queryall = 'SELECT * FROM quiz_answers WHERE id_user IN (SELECT id_user FROM quiz_answers GROUP BY id_user HAVING COUNT(id_user) > 1)'
$groupresultsbyuser = mysql_query($query, $conn) or die(mysql_error());
$rows = mysql_fetch_array($groupresultsbyuser);
$numberofrowsperuser = mysql_num_rows($groupresultsbyuser);
我被困在这里…请帮我想个办法。
试试这个:
SELECT id_user,AVG(answer)
FROM quiz_answers
GROUP BY id_user
ORDER BY AVG(answer) DESC
LIMIT 3
假设您想要显示一些用户(例如username)和高分，您可以按用户提取平均分数，然后与您的用户表(为了讨论，我称之为users并假设它有一个user_id字段)连接
SELECT u.*, scores.avgScore
FROM users u
JOIN (
SELECT id_user, AVG(answer) AS avgScore
FROM quiz_answers
GROUP BY id_user
ORDER BY avgScore DESC LIMIT 3
) scores ON u.id_user = scores.id_user