php - 返回表中 member_number 计数的列表

Question

考虑下表

我需要按降序返回一个列表，其中包含 member_nr 在表 中出现次数最多 的计数，其中 tournament = 'EPL' AND ROUND = '12'

示例

脚本应返回以下结果:

我考虑过这个问题，我的逻辑是这样的

第 1 步:逐一获取 member_nr

 $sql = "SELECT DISTINCT * 
        FROM winners 
        WHERE tournament='$tour' AND round='$round'";
LOOP(){ //get 1 member number
$mem_nr = ['mem_nr']; //assign mem_nr to variable

STEP2: GET the count(number of times) ^ABOVE^成员(member)号出现在表中

  "$sql="SELECT *, count(member_nr) as nrWins 
   FROM winners 
   where member_nr ='$memNr' and tournament='$tournament' AND     round='$round'";"
 LOOP(){//get count

第 3 步:显示数据

echo $row=['nrWins'] //Display Number Wins
echo $memNr
   }//END LOOP
 }//END LOOP

我的问题:

以上对我来说似乎不是很有效，我正在寻找最短最有效的方法来返回上表中的成员数量，欢迎任何想法/建议

Answer 1

尝试这样的事情:

SELECT *, COUNT(*) AS `wins`
FROM `winners`
WHERE `tournament` = '$tournament'
  AND `round` = '$round'
GROUP BY `member_nr`
ORDER BY `wins` DESC