Oracle数据库中如何按州筛选第二列最大值对应的行？

嘿，这个需求很常见嘛！要给每个州（第三列）挑出第二列数值最大的对应行，Oracle里有两种靠谱的实现方式，我给你拆解清楚：

方法1：使用窗口函数（推荐） #

这是现在最直观高效的解法，利用ROW_NUMBER()或RANK()窗口函数来标记每个州内的最大数值行：

-- 若只需要每个州的任意一行最大值（有并列时取其一）
SELECT aircraft_id, num, state
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY num DESC) AS row_rank
    FROM your_table t
) ranked_data
WHERE row_rank = 1;

代码解释： #

• PARTITION BY state：把数据按州（第三列）分组，每个州自成一个数据子集
• ORDER BY num DESC：在每个州的子集里，按第二列的数值从大到小排序
• ROW_NUMBER()：给每个子集内的行依次编号，数值最大的行编号为1，外层筛选row_rank = 1就能得到目标行

如果某个州存在多个并列最大值的行，想把这些行都保留下来，就把ROW_NUMBER()换成RANK()：

-- 保留所有并列最大值的行
SELECT aircraft_id, num, state
FROM (
    SELECT 
        t.*,
        RANK() OVER (PARTITION BY state ORDER BY num DESC) AS row_rank
    FROM your_table t
) ranked_data
WHERE row_rank = 1;

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

如果你的Oracle版本不支持窗口函数（比如10g以前，现在很少见了），可以用子查询先计算每个州的最大数值，再关联原表匹配结果：

SELECT t.aircraft_id, t.num, t.state
FROM your_table t
INNER JOIN (
    -- 先算出每个州的最大数值
    SELECT state, MAX(num) AS max_num
    FROM your_table
    GROUP BY state
) state_max ON t.state = state_max.state AND t.num = state_max.max_num;

这个方法的逻辑是：先通过子查询得到每个州的最大数值，再回到原表找到对应州且数值等于该最大值的行，同样会保留所有并列最大值的行。

验证你的示例数据 #

拿你给出的测试数据来看：

• WA州的最大数值是6，对应行N783AS 6 WA
• WI州的最大数值是12，对应行N496CA 12 WI

以上两种方法都会返回这两行，完全符合你期望的结果~

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