Oracle SQL单查询按用户统计资质数及获取最新资质

窗口函数的计算逻辑是为原表中每一行数据单独返回计算结果，不会做分组聚合去重。你当前的写法没有做分组或行过滤，所以有多少条原始资质记录就会返回多少行，自然持有2条资质的userid=One会返回2条记录，不符合单用户单行的预期。
另外你原SQL里的表名Qualificaitons存在拼写错误，执行时会触发对象不存在的报错，正确表名应为Qualifications。

以下两种单查询写法均可满足需求，其中第一种是Oracle场景下性能最优的原生写法：

方案1：GROUP BY + KEEP 聚合（推荐） #

直接按userid分组，用普通聚合函数统计资质总数，通过Oracle专属的KEEP子句直接取每个用户最新日期对应的资质，无需嵌套子查询：

SELECT
  userid,
  COUNT(qualification) AS count_qual,
  MAX(qualification) KEEP (DENSE_RANK FIRST ORDER BY "date" DESC) AS latest_qual
FROM Qualifications
GROUP BY userid;

语法说明：DENSE_RANK FIRST ORDER BY "date" DESC会先筛选出每个用户分组下日期最大（即最新获得）的资质记录集，再对该集合内的qualification取MAX值；如果存在同一用户同一天获得多个资质的场景，会返回名称排序最大的资质，保证结果确定性。注意date是Oracle保留关键字，作为字段名使用时需要加双引号。

方案2：窗口函数+外层过滤 #

如果偏好窗口函数写法，可以先在子查询中计算每个用户的资质总数、同时给每个用户的资质记录按获得日期倒序编排名，外层只取排名为1的最新记录即可：

SELECT userid, count_qual, qualification AS latest_qual
FROM (
  SELECT
    userid,
    qualification,
    COUNT(qualification) OVER (PARTITION BY userid) AS count_qual,
    ROW_NUMBER() OVER (PARTITION BY userid ORDER BY "date" DESC, qualification DESC) AS rn
  FROM Qualifications
)
WHERE rn = 1;

注意点：ROW_NUMBER排序时增加了qualification DESC作为兜底排序规则，避免同一用户同一天获得多条资质时返回结果不确定。

针对你提供的测试数据，上述两种SQL返回的结果完全符合预期：

• userid=One：count_qual=2，latest_qual=Qual2
• userid=Two：count_qual=1，latest_qual=Qual1
• userid=Three：count_qual=1，latest_qual=Qual2

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