有以下数据表：
select * from group_by_and_max_demo; +----+-----------+-----+-----------+ | id | name | age | city | +----+-----------+-----+-----------+ | 1 | xiaozhang | 20 | beijing | | 2 | xiaoli | 22 | shanghai | | 3 | xiaowang | 25 | beijing | | 4 | xiaoliu | 21 | shanghai | +----+-----------+-----+-----------+
先要查询每个城市中年龄最大的人的名字和id，使用如下sql：
SELECT id,`name`,MAX(age),city FROM group_by_and_max_demo GROUP BY city; +----+-----------+----------+-----------+ | id | name | MAX(age) | city | +----+-----------+----------+-----------+ | 1 | xiaozhang | 25 | beijing | | 2 | xiaoli | 22 | shanghai | +----+-----------+----------+-----------+
查询结果中beijing年龄最大的年龄是25、名字是xiaozhang、id是1，这个明显不对。实际应该是年龄25、名字xiaowang，id是3。也就是说group by后使用max，其他列并不一定是相应的行，而是当前group by分组后相关的第一条记录。
那应该如何实现这个需求呢？使用如下语句：
SELECT * FROM group_by_and_max_demo WHERE age IN(SELECT MAX(age) from group_by_and_max_demo GROUP BY city); +----+----------+-----+-----------+ | id | name | age | city | +----+----------+-----+-----------+ | 2 | xiaoli | 22 | shanghai | | 3 | xiaowang | 25 | beijing | +----+----------+-----+-----------+