Oracle SQL查询-只显示max

Question

我有一个oracle SQL查询下面的代码。此代码查找成员的用户名以及他们每年投票的次数。我如何修改它，使其只显示投票次数最多的用户/s ?

SELECT username, count(username), extract(year from voteDate) as vote_year,
max(count(*)) over (partition by extract(year from voteDate)) as Max_votes
FROM rankingInfo NATURAL JOIN memberinfo
GROUP BY username, extract(year from voteDate);

Answer 1

如果我正确理解您的查询，您的max_votes返回您想要的最大投票，您的count(username)返回每个用户的投票。如果是这样，您可以将结果放在子查询中，然后只需添加WHERE条件:

SELECT * 
FROM (
   SELECT username, count(username) votes, extract(year from voteDate) as vote_year,
       max(count(*)) over (partition by extract(year from voteDate)) as Max_votes
   FROM rankingInfo NATURAL JOIN memberinfo
   GROUP BY username, extract(year from voteDate)
) T
WHERE votes = max_votes