Oracle中如何按分组选取每组首条记录并统计数量？

看起来你在处理分组统计和排序的SQL需求时遇到了点小问题，我来帮你梳理下正确的解法~

原表 empTbl 数据 #

需求说明 #

需要按name分组，同时按照occupation降序以及该姓名的职业总数排序，最终得到如下格式的结果：

你之前尝试的问题 #

你执行的这条SQL没有覆盖全部需求：

select max(PKid) keep(dense_rank first order by occupation) PKid , name , occupation from empTbl group by name;

它只处理了按职业取第一条记录的逻辑，但没有统计每个姓名的职业总数，也没有按照总数进行排序，所以得不到预期结果。

正确的SQL语句 #

我们可以通过CTE（公共表表达式）先统计基础数据，再完成排序和序号生成：

WITH name_stats AS (
    SELECT 
        name,
        COUNT(*) AS total_count,
        -- 获取当前姓名下按职业降序排在第一位的职业
        MAX(occupation) KEEP(DENSE_RANK FIRST ORDER BY occupation DESC) AS target_occupation
    FROM empTbl
    GROUP BY name
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY total_count DESC) AS "S.no",
    name AS "Name",
    target_occupation AS "Occupation",
    total_count AS "Count"
FROM name_stats
ORDER BY total_count DESC;

逻辑拆解 #

1. 第一步：统计基础数据
   • 按name分组，用COUNT(*)算出每个姓名的职业总数total_count
   • 用KEEP(DENSE_RANK FIRST ORDER BY occupation DESC)锁定该姓名下字典序最大的职业（也就是按降序排列的第一个职业）
2. 第二步：生成最终结果
   • 用ROW_NUMBER()函数按总数降序生成序号S.no
   • 输出需求中的列，最后再按总数降序排序，就能得到你想要的结果啦

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