sql 中 max () over partition by 用法_sql 中 max () over 分区取最大值指南

要针对sql中某一列分组并取每组最大值相关记录，可使用窗口函数max() over (partition by ...)。1. 它按指定字段分组并计算每组最大值，将结果“广播”至每行；2. 可结合where条件筛选出每组最大值所在行；3. 与group by不同，它保留所有原始记录，仅新增最大值列；4. 实际应用中可配合order by、row_number()及多字段分组使用，也可替换为min()、avg()、sum()等函数；5. 若数据库不支持窗口函数，可用自连接或子查询替代，但效率较低。掌握该方法可高效处理分组取最大值问题。

在 SQL 查询中，如果你需要针对某一列进行分组，并在每组内取最大值相关的记录，MAX() OVER (PARTITION BY ...) 是一个非常实用的工具。它不仅能找出每个分组的最大值，还能将这个最大值“广播”到每一行，方便后续筛选或对比。

下面我们就来看看它的常见用法和使用技巧。

---

什么是 MAX() OVER (PARTITION BY ...)

MAX() OVER (PARTITION BY col) 是一种窗口函数的写法，意思是：按照指定的字段（col）进行分组，在每一组内部计算该字段的最大值，并把这个最大值返回给组内的每一行数据。

举个简单的例子：

假设你有一张销售表，记录了不同销售人员在不同区域的销售额：

region	salesperson	amount
North	Alice	100
North	Bob	150
South	Carol	200

你想知道每个区域的最大销售额是多少，同时保留每个人的原始记录。这时候就可以这样写：

SELECT *,
       MAX(amount) OVER (PARTITION BY region) AS max_amount_per_region
FROM sales;

结果会是：

region	salesperson	amount	max_amount_per_region
North	Alice	100	150
North	Bob	150	150
South	Carol	200	200

你可以看到，每个区域的最大值都“复制”到了每一行上，方便后续比较。

---

如何用它筛选出每组最大值对应的记录

有时候我们不只是想看到最大值，还想筛选出哪些记录是每组的最大值所在行。

这就需要结合 WHERE 条件来使用上面的结果：

SELECT *
FROM (
    SELECT *,
           MAX(amount) OVER (PARTITION BY region) AS max_amount
    FROM sales
) t
WHERE amount = max_amount;

这样就能得到：

region	salesperson	amount
North	Bob	150
South	Carol	200

注意：如果一个区域内有多个销售人员金额相同且都是最大值，这个方法会把他们都列出来。

---

和 GROUP BY 的区别在哪？

很多初学者会问：既然可以用 GROUP BY region 找出每个区域的最大销售额，为什么还要用窗口函数？

其实两者用途不同：

• GROUP BY 是聚合操作，最终只返回每个分组的一行数据；
• MAX() OVER (PARTITION BY ...) 是窗口函数，保留所有原始行，只是多加一列最大值信息。

所以，如果你想在保留原始数据的同时做比较、筛选，那窗口函数更合适。

---

实际应用中的几个小技巧

• 如果你需要按时间排序后取最新一条记录，可以配合 ORDER BY 和 ROW_NUMBER() 使用；
• 多字段分组也没问题，比如 PARTITION BY region, year；
• 不止 MAX()，MIN()、AVG()、SUM() 等都可以搭配 OVER (PARTITION BY ...) 使用；
• 如果数据库不支持窗口函数（如 MySQL 8.0 之前），可以用自连接或子查询实现类似功能，但效率通常不如窗口函数高。

---

基本上就这些。掌握好 MAX() OVER (PARTITION BY ...)，你会发现处理分组最大值问题时轻松不少。