MySQL where子句mysql中的MAX函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22538287/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MAX function in where clause mysql
提问by user3176971
How can I use max() function in where clause of a mysql query, I am trying:
如何在 mysql 查询的 where 子句中使用 max() 函数，我正在尝试：
select firstName,Lastname,MAX(id) as max where id=max;
this is giving me an error:
这给了我一个错误：
Unknown column 'max' in 'where clause'
Any Help? Thanks in advance.
任何帮助？提前致谢。
回答by spencer7593
We can't reference the result of an aggregate function (for example MAX()) in a WHEREclause of the same SELECT.
我们不能MAX()在同一WHERE个子句中引用聚合函数（例如）的结果SELECT。
The normative pattern for solving this type of problem is to use an inline view, something like this:
解决此类问题的规范模式是使用内联视图，如下所示：
SELECT t.firstName
, t.Lastname
, t.id
FROM mytable t
JOIN ( SELECT MAX(mx.id) AS max_id
FROM mytable mx
) m
ON m.max_id = t.id
This is just one way to get the specified result. There are several other approaches to get the same result, and some of those can be much less efficient than others. Other answers demonstrate this approach:
这只是获得指定结果的一种方式。还有其他几种方法可以获得相同的结果，其中一些可能比其他方法效率低得多。其他答案证明了这种方法：
WHERE t.id = (SELECT MAX(id) FROM ... )
Sometimes, the simplest approach is to use an ORDER BY with a LIMIT. (Note that this syntax is specific to MySQL)
有时，最简单的方法是使用带有 LIMIT 的 ORDER BY。（请注意，此语法特定于 MySQL）
SELECT t.firstName
, t.Lastname
, t.id
FROM mytable t
ORDER BY t.id DESC
LIMIT 1
Note that this will return only one row; so if there is more than one row with the same id value, then this won't return all of them. (The first query will return ALL the rows that have the same id value.)
请注意，这将只返回一行；所以如果有多于一行具有相同的 id 值，那么这不会返回所有的行。（第一个查询将返回所有具有相同 id 值的行。）
This approach can be extended to get more than one row, you could get the five rows that have the highest id values by changing it to LIMIT 5.
这种方法可以扩展为获得不止一行，您可以通过将其更改为 来获得具有最高 id 值的五行LIMIT 5。
Note that performance of this approach is particularly dependent on a suitable index being available (i.e. with idas the PRIMARY KEY or as the leading column in another index.) A suitable index will improve performance of queries using all of these approaches.
请注意，此方法的性能特别取决于可用的合适索引（即id作为 PRIMARY KEY 或作为另一个索引中的前导列）。合适的索引将提高使用所有这些方法的查询的性能。
回答by Fabian Bigler
Use a subselect:
使用子选择：
SELECT row FROM table WHERE id=(
SELECT max(id) FROM table
)
Note: ID must be unique, else multiple rows are returned
注意：ID 必须唯一，否则返回多行
回答by Hemanathagiribabu
SELECT firstName, Lastname, MAX(id) as max WHERE YOUR_CONDITIONS_HERE HAVING id=max(id)
回答by jasonseminara
Some Mysql versions disallow 'limit' inside of a sub select. My answer to you (and me in the future) would be to use groups
某些 Mysql 版本不允许在子选择中使用“限制”。我对你（以及未来的我）的回答是使用组
select firstName,Lastname,id
where {whatever}
group by id
having max(id)
This allows you to return whatever you want in the select area, without having an aggregate field.
这允许您在选择区域中返回任何您想要的内容，而无需聚合字段。
回答by AgRizzo
Do you want the first and last name of the row with the largest id?
您想要具有最大 id 的行的名字和姓氏吗？
If so (and you were missing a FROM clause):
如果是这样（并且您缺少 FROM 子句）：
SELECT firstname, lastname, id
FROM foo
ORDER BY id DESC
LIMIT 1;
回答by ucsunil
The syntax you have used is incorrect. The query should be something like:
您使用的语法不正确。查询应该是这样的：
SELECT column_name(s) FROM tablename WHERE id = (SELECT MAX(id) FROM tablename)
回答by michael.moyer
This query should give you back the data you want. Replace foo with the table name you are using.
这个查询应该给你你想要的数据。将 foo 替换为您正在使用的表名。
SQL Query:
SQL查询：
select firstName,Lastname, id
from foo
having max(id) = id
回答by bluszcz
You are using word 'max' as an alias for your column. Try to:
您正在使用单词“max”作为列的别名。尝试：
MAX(id) as mymax ... WHERE ID - mymax
