我一直在同一行中尝试GROUP BY
和ORDER BY
来获得我想要的东西,但它不起作用。我使用了一个while循环,它运行了数千个名字,检查每个城市的最高点。如何在不重复同一个城市两次的情况下,从每个城市获得得分最高的名字?
这就是我数据库中的内容(简而言之):
ID City Points Name
1 NYC 16 Stan
2 London 24 Paul
3 NYC 11 Jeffrey
4 London 20 George
5 NYC 18 Ryan
$query = "SELECT `ID`, `City`, `Points`, `Name` FROM `table` GROUP BY `City` ORDER BY `Points`";
给我:
1 NYC 16 Stan
2 London 24 Paul
我想要它给我的:
2 London 24 Paul
5 NYC 18 Ryan
您可以使用左联接作为
select t1.* from table_name t1
left join table_name t2 on t1.city=t2.city and t1.points < t2.points
where t2.id is null;
或者使用不相关子查询:
select t.* from table_name t
join (
select max(points) as points,city from table_name group by city
)x on x.city=t.city and x.points = t.points ;
在此处查看文档https://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
或子查询
select t.* from table_name t
where not exists (
select 1 from test t1 where t.city = t1.city and t.points < t1.points
);
这是组最大值,也是最常见的SQL问题之一。你可以试试这样的东西,
SELECT tab1.*
FROM @Table AS tab1
LEFT JOIN @Table AS tab2
ON tab1.City=tab2.city AND tab2.points > tab1.points
WHERE tab2.City IS NULL;