mysql的where子句中的MAX函数 [英] MAX function in where clause mysql

问题描述

我正在尝试如何在mysql查询的where子句中使用max()函数:

 select firstName,Lastname,MAX(id) as max where id=max;

这给我一个错误:

Unknown column 'max' in 'where clause'

有帮助吗?预先感谢.

解决方案

我们无法在同一SELECT的WHERE子句中引用聚合函数(例如MAX())的结果.

解决此类问题的规范模式是使用内联视图，如下所示:

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

这只是获得指定结果的一种方法.还有其他几种方法可以获得相同的结果，其中一些方法的效率可能比其他方法低得多.其他答案证明了这种方法:

 WHERE t.id = (SELECT MAX(id) FROM ... )

有时候，最简单的方法是使用带有LIMIT的ORDER BY. (请注意，此语法特定于MySQL)

SELECT t.firstName
     , t.Lastname
     , t.id
  FROM mytable t
 ORDER BY t.id DESC
 LIMIT 1

请注意，这只会返回一行；因此，如果有多个具有相同ID值的行，则该行将不会全部返回. (第一个查询将返回所有具有相同ID值的行.)

此方法可以扩展为获取多行，您可以通过将其更改为LIMIT 5来获得具有最高ID值的五行.

请注意，此方法的性能特别取决于可用的合适索引(即，将id作为PRIMARY KEY或另一个索引中的前导列.)合适的索引将使用所有这些来提高查询的性能.方法.

How can I use max() function in where clause of a mysql query, I am trying:

 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.

解决方案

We can't reference the result of an aggregate function (for example MAX() ) in a WHERE clause of the same 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)

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.)

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.

Note that performance of this approach is particularly dependent on a suitable index being available (i.e. with id as the PRIMARY KEY or as the leading column in another index.) A suitable index will improve performance of queries using all of these approaches.

这篇关于mysql的where子句中的MAX函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！