我正在尝试从此页面上对数据进行排序:http://www.excelwrestling.com/sortbymostwinsbyweight.php
我希望表格按重量排序,然后按该重量中获胜最多的名称排序。 另外,我想再添加一列,显示该摔跤手的胜利次数。
这是我从查看教程中获得的,但它们的排序不正确:
$query="SELECT *,Winner, COUNT(*) as count FROM results GROUP BY Winner ORDER BY Weight ASC ";
$result=mysql_query($query);
谢谢!
SELECT ww.*
FROM
( SELECT Weight, Winner, COUNT(*) AS Wins
FROM results
GROUP BY Weight, Winner
) AS ww
JOIN
( SELECT DISTINCT Weight
FROM results
) AS dw
ON ww.Weight = dw.Weight
AND ww.Winner =
( SELECT r.Winner
FROM results AS r
WHERE r.Weight = dw.Weight
GROUP BY r.Winner
ORDER BY COUNT(*) DESC
LIMIT 1
) ;
(Weight, Winner)
索引将有助于查询的效率。
您也可以使用此查询获得正确的结果,但我不建议使用它,因为它使用 MySQL 的一些非标准功能和行为,因此将来可能会中断:
SELECT Weight, Winner, Wins
FROM
( SELECT Weight, Winner, COUNT(*) AS Wins
FROM results
GROUP BY Weight, Winner
ORDER BY Weight, Wins DESC
) AS ww
GROUP BY Weight ;
你只是按winner
订购,当你说你的要求是"最多赢"时。这意味着您应该拥有:
SELECT ...
FROM ...
GROUP BY Winner
ORDER BY Weight ASC, count DESC
^^^^^^^^^^^^^---- order by the aggregate results.
请注意,我假设您希望权重按升序排列,并按降序获胜。