获取最大行(列) [英] Get row with max(column)

问题描述

每当我使用 max 函数时，我都会以某种方式失去与其他值的所有连接，因此稍后打印的行不再与我运行 max 的列相关.

Whenever I use the max function I somehow lose all the connection to my other values, so that the row that is printed later doesn't correlate with the column that I ran max on anymore.

所以我的表是:

user col1 col2 col3
1    1    2    3
1    3    4    5
2    2    3    1
3    1    1    3
3    2    4    6
4    5    1    5

所以如果我跑

select user, col1, col2, max(col3) as col3
from table
group by user
order by user;

我会得到

user col1 col2 col3
1    1    2    5
2    2    3    1
3    1    1    6
4    5    1    5

所以 col3 的最大值是正确的，但它没有得到该值的正确行.

So the max value of col3 is correct, but it doesn't get the correct row of that value.

我想要的是获取列的最大值并为每个用户返回该行.如果有多个最大值，它应该返回所有用户，即使它具有相同的用户 ID.

What I want is to get the max value of a column and return that row for each user. If there are multiple max values that it should return all users, even if it has same user id.

推荐答案

其他数据库(例如 MS SQL Server)不允许您将聚合值与非聚合值混合在一起，仅仅因为您会得到错误的结果.

Other databases (e.g. MS SQL Server) doesn't let you mix aggergated values with non-aggregated values, just because you would get the wrong result.

因此，如果您想从最大值所在的记录中获取非聚合值，请再次连接该表:

So, if you want non-aggregated values from the record where the maximum value was, join against the table again:

select x.user, y.col1, y.col2, x.col3
from (
  select user, max(col3) as col3
  from table
  group by user
) x
inner join table y on y.user = x.user and y.col3 = x.col3
order by x.user

这篇关于获取最大行(列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！