如何在非聚合SQL查询中基于另一列返回最大值

嘿，我懂你遇到的问题了——在带GROUP BY的聚合查询里用ARRAY_AGG凑最大值还行，但放到普通非聚合查询里就完全不是你想要的效果对吧？其实要实现「基于某一列的分组逻辑，给每一行记录都带上对应分组里另一列的最大值」，用窗口函数或者子查询关联就搞定了，我给你举个具体场景和两种常用方法：

假设我们有一张user_orders表，结构如下：

我们的需求是：给每一条订单记录，同时显示该用户历史订单中的最大金额，而不是只分组显示每个用户的最大值。

方法1：用窗口函数MAX() OVER()（推荐） #

这是最简洁高效的方式，窗口函数可以在不使用GROUP BY的前提下，为每一行计算对应分组的聚合值。

SELECT 
  user_id,
  order_id,
  order_amount,
  order_date,
  -- 按user_id分组，计算该组的order_amount最大值
  MAX(order_amount) OVER (PARTITION BY user_id) AS user_max_order_amount
FROM user_orders;

执行后结果会是这样：

完美保留了所有原始订单记录，同时每一行都带上了对应用户的最大订单金额。

方法2：子查询关联（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如一些比较老的MySQL版本），可以用子查询预先计算每个用户的最大值，再通过关联把结果带回去。

SELECT 
  uo.user_id,
  uo.order_id,
  uo.order_amount,
  uo.order_date,
  um.max_amount AS user_max_order_amount
FROM user_orders uo
-- 子查询先按用户分组算出每个用户的最大订单金额
JOIN (
  SELECT user_id, MAX(order_amount) AS max_amount
  FROM user_orders
  GROUP BY user_id
) um ON uo.user_id = um.user_id;

这个方法的结果和上面完全一致，只是写法稍微繁琐一点，但兼容性更好。

为啥ARRAY_AGG在非聚合查询里没达到预期？ #

ARRAY_AGG的作用是把分组内的某列值聚合成一个数组，如果你在没有GROUP BY的查询里直接用它，默认会把整个表的所有值聚合成一个数组（比如SELECT ARRAY_AGG(order_amount) FROM user_orders会得到所有订单金额的数组），而不是按你想要的用户分组。

当然你也可以用ARRAY_AGG(order_amount) OVER (PARTITION BY user_id)来得到每个用户的订单金额数组，但要从数组里取最大值还得套一层MAX(UNNEST(ARRAY_AGG(...)))，这就绕远路了，完全不如直接用MAX()窗口函数高效。

内容的提问来源于stack exchange，提问作者Simon Breton