sql - 我需要在每年的 Oracle SQL 查询中找到最高投票者

Question

我有一张表，可以说是排名信息，其中有

username   mvid  votedate
john        1    23-sep-90
john        2    23-sep-90
smith       1    23-sep-90
john        3    24-oct-91
smith       3    24-oct-91
smith       4    25-dec-91
smith       5    25-dec-91

我需要在 sqldeveloper(Oracle) 中编写一个 sql 查询，它将给出每年投票数最多的成员。输出应为用户名、年份、每年的投票总数。让我们考虑一下上面的例子:我需要这样的输出。

username  year  number_Of_Votes
john       1990    2
smith      1991    3

因为 1990 年约翰以 1 票击败了史密斯，而 1991 年史密斯以 2 票击败了约翰。

我统计了所有选票，但无法获得一年内的最大票数。

这就是我所做的:

select r1.username, 
    Extract(year from r1.votedate)"Year",
    count(username)
from rankinginfo r1
where Extract(year from r1.votedate)  is not null
group by Extract(year from r1.votedate), 
      r1.username;
order by  Extract(year from r1.votedate),
      username;

Answer 1

select  *
from    (
        select  VotesPerUserPerYear.*
        ,       dense_rank() over (
                    partition by voteyear
                    order by votecount desc) as rn
        from    (
                select  username
                ,       extract(year from votedate) as voteyear
                ,       count(*) as votecount
                from    YourTable
                group by
                        username
                ,       extract(year from votedate)
                ) VotesPerUserPerYear
        ) SubQueryWithRank
where   rn = 1 -- Only top voter per year

Example at SQL Fiddle.