php - 在mysql中使用max(points) AS时如何获得完整的行？

Question

我正在使用此代码。

$query = "SELECT max(Points) AS Points,City FROM Table_name WHERE Gender='Female' && Country='England' GROUP BY City";

我希望它给出完整的行，而不仅仅是积分和城市。在我的数据库中，顺序是 ID、城市、积分、姓名、性别、国家/地区。当我运行这行代码时，它只给我积分和城市。

I have tried * but doesn't work.

Answer 1

我猜您想要具有最高分值的整行，即包含女性/英格兰/城市的行，其中没有人具有更高的分值:

SELECT *
FROM Table_name t1
WHERE Gender='Female' 
 AND Country='England'
 AND NOT EXISTS (select 1 from Table_name t2
                 WHERE t2.Gender='Female' 
                   AND t2.Country='England'
                   AND t2.city = t1.city
                   AND t2.points > t1.points)

如果平局，将显示两行。

要仅返回一行/城市，只需在 EXISTS 中添加

SELECT *
FROM Table_name t1
WHERE Gender='Female' 
 AND Country='England'
 AND NOT EXISTS (select 1 from Table_name t2
                 WHERE t2.Gender='Female' 
                   AND t2.Country='England'
                   AND t2.city = t1.city
                   AND t2.points > t1.points
                   AND t2.id < t1.id)