mysql:select max(score)不返回相关的行数据

问题描述：

例如，如果我有一个得分表:

if i have for example a scores table:


    user game score timestamp
    1    50   50    date
    2    60   40    date
    3    70   25    date
    4    80   18    date

然后我运行查询:

select user, game, max(score), timestamp from scores

我将获得最大分数20，但是返回的其余列并非来自同一行.

i will receive the maximum score number that is 20, but the rest of the columns that are returned are not from that same row.

答

您正在使用MAX，它是一个聚合函数.聚合函数具有将表中的多行视为一个组的作用.如果您不执行任何特殊操作，则整个表中的所有行都将被用作一个大组，并且当存在类似MAX的聚集函数时，所有这些行都将被压缩为一个聚合行.对于其他聚合函数(例如MIN，SUM，GROUP_CONCAT和朋友)，也会产生这种压缩效果(请参阅:

You are using MAX, which is an aggregate function. Aggregate functions have the effect of treating multiple rows in the table as a group. If you don't do anything special, all rows in the entire table will be used as one big group, and when an aggregrate function like MAX is there, all these rows will be condensed into just one aggregate row. That condensing effect would have occurred also for other aggregate functions like MIN, SUM, GROUP_CONCAT and friends (see: http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html). You can also apply specific groupings using the GROUP BY construct, but if you don't the occurrence of the aggregate function will just bunch up all rows into one row (but this bunching-up occurs after applying the WHERE condition, so only the filtered rows are aggregated)

现在，由于聚合函数的这种压缩或减少"效应，因此存在某种从多个值中选出一个值的方法.对于MAX，这种方式是仅列出在作为参数传递给MAX的表达式的所有实例中找到的最大值.但是您的其他列没有这样的聚合函数.对于大多数数据库产品，在SELECT列表中同时出现未聚集列和聚集列都将是一个错误.但是MySQL的行为是错误的/不同的，并且只为SELECT位中列出的每个非聚合表达式返回可用值之一.哪个值取决于mysql-您不能依赖任何特定的算法.

Now, because of this condensing or 'reducing' effect of aggregate functions, there as to be some way to make one value out of many values. For MAX, that way is to list only the maximum value found for all instances of the expression you passed as argument to MAX. But your other columns do not have such an aggregate function. For most database products, the occurrence of both unaggregated as well as aggregated columns in the SELECT list would be an error. But MySQL behaves wrongly/differently and returns just one of the available values for each non-aggregated expression listed in the SELECT bit. Which value is up to mysql - you can't rely on any particular algorithm.

在许多情况下，人们希望对具有最大值的任何行"执行某些操作，换句话说，找到具有最大值的行，但使用该行中未聚合的其他列. middaparka提供的解决方案可以做到这一点，还有其他方法也可以实现(谷歌针对MySQL分组最大).有关聚合函数和相关的GROUP BY子句的更多常规信息，您可以在此处查看-shameless selfplug-我的文章:

In many cases, people want to do something with "whatever row that has the maximum value", in other words, find the row that has as value the maximum value, but use the other columns from that row unaggregated. The solution provided by middaparka does that, and there are otherways to achieve that too (google for MySQL group-wise maximum). For more general information about aggregate functions and the related GROUP BY clause, you could take a look at -shameless selfplug- my article here: http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html

mysql:select max(score)不返回相关的行数据

相关推荐