如何按C1分组并筛选出每组N1取最大值的所有行

嘿，我来帮你搞定这个需求！针对你提供的Oracle示例数据，要实现按C1分组，取出每组中N1取最大值的所有行，这里有两种常用且高效的方法：

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

利用RANK()窗口函数可以轻松标记出每组内N1最大的行，这种方法简洁且性能不错，尤其是在数据量较大时：

WITH TB AS(
 SELECT 'A' C1, 'B' AS C2, 1 AS N1, 1 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A' C1, 'B' AS C2, 2 AS N1, 2 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 1 AS N1, 3 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 2 AS N1, 4 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 3 AS N1, 1 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A2' C1, 'B2' AS C2, 1 AS N1, 6 AS N2 FROM DUAL
),
ranked_tb AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY C1 ORDER BY N1 DESC) AS rnk
    FROM TB
)
SELECT C1, C2, N1, N2
FROM ranked_tb
WHERE rnk = 1;

说明： #

• PARTITION BY C1：按C1进行分组
• ORDER BY N1 DESC：在每组内按N1降序排列，最大值排在最前面
• RANK()：给每组内的行排名，最大值对应的行排名为1，如果同一组内有多个相同的最大值，它们的排名都是1，都会被保留

方法二：使用子查询关联 #

这种方法先通过子查询找出每组C1对应的最大N1，再将原表与这个子查询结果关联，筛选出符合条件的行：

WITH TB AS(
 SELECT 'A' C1, 'B' AS C2, 1 AS N1, 1 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A' C1, 'B' AS C2, 2 AS N1, 2 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 1 AS N1, 3 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 2 AS N1, 4 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A1' C1, 'B1' AS C2, 3 AS N1, 1 AS N2 FROM DUAL
 UNION ALL
 SELECT 'A2' C1, 'B2' AS C2, 1 AS N1, 6 AS N2 FROM DUAL
)
SELECT t.*
FROM TB t
JOIN (
    SELECT C1, MAX(N1) AS max_n1
    FROM TB
    GROUP BY C1
) m ON t.C1 = m.C1 AND t.N1 = m.max_n1;

说明： #

• 子查询(SELECT C1, MAX(N1) AS max_n1 FROM TB GROUP BY C1)：计算每个C1分组对应的最大N1值
• 主查询通过JOIN关联原表和子查询，筛选出C1匹配且N1等于对应分组最大值的行

执行结果 #

两种方法都会返回如下结果：

内容的提问来源于stack exchange，提问作者Hong Van Vit