如何在mysql表中选择最大值行 [英] How to select a maximum value row in mysql table

问题描述

我有下表

表结构:

CREATE TABLE IF NOT EXISTS `people` ( 
`name` varchar(10) NOT NULL, 
`age` smallint(5) unsigned NOT NULL, 
PRIMARY KEY (`name`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

插入一些值:

INSERT INTO `people` (`name`, `age`) VALUES 
('bob', 13), 
('john', 25), 
('steve', 8), 
('sue', 13); 

已执行的查询:

SELECT MAX(  `age` ) ,  `name` FROM  `people` WHERE 1

预期结果:

25, John

生成的结果

25, bob

我们可以通过使用此查询来实现

We can achieve this by using this query

SELECT `age`,  `name` FROM  `people` ORDER BY age DESC LIMIT 1

问题1:我在这里犯了什么错误，为什么这个MAX函数没有返回相关的行信息?

问题2:使用哪种方法可以提高MAX函数或ORDER BY子句的性能?

推荐答案

问题1:我在这里犯了什么错误，为什么这个MAX函数没有返回相关的行信息?

Question 1 : What I made mistake here and why this MAX function is not return the relevant row information?

您需要阅读group by子句.

MySQL的容忍度超出了应有的程度，在此过程中造成了混乱.基本上，任何没有聚合的列都应包含在group by子句中.但是MySQL语法糖允许忘记"列.当您这样做时，MySQL从其分组依据的集合中吐出一个任意值.在您的情况下，集合中的第一行是bob，因此它将返回该行.

MySQL is being a lot more permissive than it should, introducing confusion in the process. Basically, any column without an aggregate should be included in the group by clause. But MySQL syntactic sugar allows to "forget" columns. When you do, MySQL spits out an arbitrary value from the set that it's grouping by. In your case, the first row in the set is bob, so it returns that.

问题2:使用哪种方法可以提高MAX函数或ORDER BY子句的性能?

Question 2: Which one is good to use, to increase performance MAX function or ORDER BY clause?

您的第一个语句(使用不​​带group by的max())完全不正确.

Your first statement (using max() without a group by) is simply incorrect.

如果您要使用最早的用户之一，则order by age desc limit 1是继续操作的正确方法.

If you want one of the oldest users, order by age desc limit 1 is the correct way to proceed.

如果要使用所有最早的用户，则需要一个子选择:

If you want all of the oldest users, you need a subselect:

SELECT p.* FROM people p WHERE p.age = (select max(subp.age) from people subp);

这篇关于如何在mysql表中选择最大值行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！