sql-server - SQL Server - 每个地区的顶级销售人员

Question

    SELECT region, person, sum(dollars) as thousands
    FROM sales
    GROUP BY region, person
    ORDER BY region, sum(dollars) desc

上面的 SQL 生成了每个区域的完整销售人员列表，如下所示

    region person      thousands

    canada mike smith  $114
    canada joe blog    $76
    canada pete dodd   $45
    usa    john doe    $253
    usa    jane smyth  $120
    europe pieter tsu  $546
    europ  mike lee    $520

如果我只想展示每个地区的顶级销售人员(如下所示)，我怎样才能最好地做到这一点？

    region person      thousands

    canada mike smith  $114
    usa    john doe    $253
    europe pieter tsu  $546

Answer 1

我已经做了一些像burnall建议的。我对“top 1 with ties”部分不太喜欢，所以我把整个事情变成了一个子查询，并选择了ranking = 1的行。

select *
from
(
     select region, 
            person, 
            rank() over(partition by region order by sum(dollars) desc) as ranking
     from sales 
     group by region, 
              person 

) temp
where ranking = 1

请注意，这也适用于平局，因为 rank() 似乎对相等的总和进行了相同的排名。