SQL Server 2012:根据多个条件选择前 n 个 [英] SQL Server 2012: Select Top n based on multiple criteria

问题描述

这里对 SQL 非常陌生 - 非常感谢您的帮助.我有一个包含 Region、Month、Member ID 和 Sales(每个成员有多个交易)的表格.我只想根据每个地区、每月的销售额总和来提取前 2 名成员......所以本质上:

Pretty new to SQL here - help would be much appreciated. I have a table with Region, Month, Member ID, and Sales (with multiple transactions per member). I just want to extract the top 2 members, based on sum of sales, per region, per month....so essentially:

Region   Month     MemberID   Sales
-----------------------------------------
  1      1/1/2013     A       $200 
  2      2/1/2013     B       $300 
  1      1/1/2013     A       $100 
  1      1/1/2013     B        $50 
  2      1/1/2013     D       $500 
  2      2/1/2013     C       $200 

变成:

Region  Month   Member ID   Sales
-----------------------------------------
1   1/1/2013    A    $300 
1   1/1/2013    B    $50 
2   1/1/2013    D    $500 
2   1/1/2013    B    $200 

最终，将有 10 个区域，我想取每个区域每个月的会员销售额前 5 名.

Ultimately, there will be 10 regions, and I'd like to take the top 5 sales by member for each region, each month.

推荐答案

你可以用 row_number() 做到这一点:

You can do this with row_number():

select region, month, MemberId, sales
from (select region, month, MemberId, sum(sales) as sales
             row_number() over (partition by region, month order by sum(sales) desc) as seqnum
      from table t
      group by region, month, MemberId
     ) t
where seqnum <= 2;

这篇关于SQL Server 2012:根据多个条件选择前 n 个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！