如何编写更高效的SQL查询：分组取最大值关联对应字段

现有一张包含col1、col2、col3三列的表，数据如下：

col1        col2        col3
1           green        10    
1           blue         15
3           red          20
3           yellow        5
4           purple       17
4           black        11 

需求为按col1分组，获取每组col3的最大值，同时获取该最大值所在行的col2值，期望得到的结果集如下：

col1        col2        col3   
1           blue         15
3           red          20
4           purple       17

我想到的一种实现方式如下（若能正常运行）：

SELECT col1, col3, col2 
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col3 DESC) AS rn, col1, col3, col2

) sub
WHERE sub.rn = 1;

但该写法感觉过于冗余，想了解是否存在更优、更高效的实现方式？

你的写法本身是可行的，但可以根据数据库特性选择更简洁或高效的方案，以下几种方式供参考：

1. 用QUALIFY简化窗口函数逻辑（通用现代数据库） #

PostgreSQL 12+、MySQL 8.0+、BigQuery等支持QUALIFY子句的数据库，可以直接用它筛选窗口函数结果，省去子查询嵌套：

SELECT col1, col2, col3
FROM your_table
QUALIFY ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col3 DESC) = 1;

QUALIFY能直接对窗口函数的计算结果做过滤，代码更紧凑。

2. 关联子查询（适配旧版无窗口函数的数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用分组子查询关联原表匹配最大值：

SELECT t1.col1, t1.col2, t1.col3
FROM your_table t1
JOIN (
    SELECT col1, MAX(col3) AS max_col3
    FROM your_table
    GROUP BY col1
) t2 ON t1.col1 = t2.col1 AND t1.col3 = t2.max_col3;

注意：如果同一col1组内有多个col3等于最大值的行，这个写法会返回所有符合条件的行；若要每组只返回一行，可添加DISTINCT或结合数据库的行限制语法（比如LIMIT 1加分组）。

3. FETCH FIRST WITH TIES语法（Oracle、SQL Server等） #

Oracle 12c+、SQL Server 2012+支持该语法，可直接获取每组的最大值行：

SELECT col1, col2, col3
FROM your_table
ORDER BY ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col3 DESC)
FETCH FIRST 1 ROWS WITH TIES;

这个写法无需嵌套子查询，逻辑简洁高效。

4. 保留所有最大值行的方案 #

如果需要保留同一组内所有col3等于最大值的行，而非只取一行，可以用MAX() OVER窗口函数：

SELECT DISTINCT col1, col2, col3
FROM (
    SELECT 
        col1, col2, col3,
        MAX(col3) OVER (PARTITION BY col1) AS group_max_col3
    FROM your_table
) t
WHERE col3 = group_max_col3;

对比ROW_NUMBER()的写法，这个方案不会丢弃同组内的其他最大值行。

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