sql-server-2008 - SQL SELECT TOP N 每个 ID、每个时间、每个组

Question

我真的很难编写存储过程。
这是这种情况。

我在一个存储过程中加入多个表并将其存储在一个临时表中。
下面是存储过程给出的结果的说明:

CLIENT        Request     Month   Total

Client A      A           1       1
Client A      B           1       3
Client A      C           1       9
Client A      D           1       4 
Client A      E           1       6

Client A      A           2       6
Client A      B           2       9
Client A      C           2       1
Client A      D           2       3 
Client A      E           2       2

Client B      A           1       3
Client B      B           1       7
Client B      C           1       9
Client B      D           1       8 
Client B      E           1       4

Client B      A           2       5
Client B      B           2       8
Client B      C           2       1
Client B      D           2       1 
Client B      E           2       3

我的问题是，如何根据最高总请求获得每个客户每月的前 N ​​个(例如:前 3 个)。

我将在 Reporting Service 中为图表使用存储的 procudere，我也很难在图表中使用过滤器功能，所以我认为有可能首先从 sql 查询中过滤它。

我希望我和上面的插图清楚地解释了这个问题。我真的很感激你的每一个回应。
之前非常感谢

Answer 1

DECLARE @TABLE Table
(
    CLIENT varchar(100),Request varchar(10),[Month] int,Total int
)

Insert into @Table

Select 'Client A','A',1,1 UNION ALL
Select 'Client A','B',1,3 UNION ALL
Select 'Client A','C',1,9 UNION ALL
Select 'Client A','D',1,4 UNION ALL
Select 'Client A','E',1,6 UNION ALL
Select 'Client A','A',2,6 UNION ALL
Select 'Client A','B',2,9 UNION ALL
Select 'Client A','C',2,1 UNION ALL
Select 'Client A','D',2,3 UNION ALL
Select 'Client A','E',2,2 UNION ALL
Select 'Client B','A',1,3 UNION ALL
Select 'Client B','B',1,7 UNION ALL
Select 'Client B','C',1,9 UNION ALL
Select 'Client B','D',1,8 UNION ALL
Select 'Client B','E',1,4 UNION ALL
Select 'Client B','A',2,5 UNION ALL
Select 'Client B','B',2,8 UNION ALL
Select 'Client B','C',2,1 UNION ALL
Select 'Client B','D',2,1 UNION ALL
Select 'Client B','E',2,3

Declare @Top int
Set @Top=2

;with CTE AS
(
    SELECT CLIENT,Request,[Month],Total,
    --month wise top record based on total request 
    ROW_NUMBER()Over(Partition by CLIENT,[Month] Order by  Total Desc) AS RN
    FROM @Table
)
Select *
From CTE
where rn<=@Top
Order by CLIENT