这个SQL问题的最佳解决方案 - best solution in this sql question

best solution in this sql question

我有以下sql查询在我的头文件:

// This query is getting points scored by user1, this number changes
// each time user scores more points
$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user1 AND points= '1'");
$points = mysql_num_rows($query_points);

现在，如果有3个或更多的用户登录，我如何比较他们的个人积分，如果我想?

我是否要像这样写3遍这个sql查询:

$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user1 AND points= '1'");
$points1 = mysql_num_rows($query_points);
$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user2 AND points= '1'");
$points2 = mysql_num_rows($query_points);
$query_points = mysql_query("SELECT * FROM quiz WHERE `sender` = user3 AND points= '1'");
$points3 = mysql_num_rows($query_points);

比较$points1， $points2和$points3和post结果?还是有更好的方法?

使用COUNT, GROUP BY, ORDER BY和LIMIT:

  SELECT sender
    FROM quiz
   WHERE points = 1
GROUP BY sender
ORDER BY COUNT(*) DESC
   LIMIT 3

它将选择得分最高的前三个发送者。

顺便说一下，如果你只需要行数，不要做SELECT *查询，而使用mysql_num_rows。相反，只需执行SELECT COUNT(*)查询。

使用组by返回一次查询中用户的计数。

select sender, count(case when points = 1 then 1 end)
from quiz
where sender IN ('user1', 'user2', 'user3')
group by sender
order by count(*)