【求助】求一个分类排序前几名的查询语句（自带测试数据表）

[iamggggs]

测试表结构如下：

;with Test as 
(
 select '1' as 客户代码,101 as 佣金,'A' as 经纪人 union all
 select '2' as 客户代码,102 as 佣金,'B' as 经纪人 union all
 select '3' as 客户代码,103 as 佣金,'C' as 经纪人 union all
 select '4' as 客户代码,104 as 佣金,'A' as 经纪人 union all
 select '5' as 客户代码,105 as 佣金,'B' as 经纪人 union all
 select '6' as 客户代码,106 as 佣金,'C' as 经纪人 union all
 select '7' as 客户代码,107 as 佣金,'A' as 经纪人 union all
 select '8' as 客户代码,108 as 佣金,'B' as 经纪人 union all
 select '9' as 客户代码,109 as 佣金,'C' as 经纪人
)
select * from Test
/*结果
客户代码	佣金	经纪人
1	        101	A
2	        102	B
3	        103	C
4	        104	A
5	        105	B
6	        106	C
7        	107	A
8	        108	B
9	        109	C
*/

查询要求：查询出每个[经纪人名]下[客户代码]的[佣金]前N名，比如前两名，在一个表中体现。

[卖水果的net]

with m as (
select row_number() over(partition by 经纪人 order by 佣金 desc) rn, * from Test
)
select * from m where rn <= 2 

[iamggggs]

引用 1 楼 wmxcn2000 的回复:

with m as (
select row_number() over(partition by 经纪人 order by 佣金 desc) rn, * from Test
)
select * from m where rn <= 2 

十分感谢，第一次见识了over partition by的运用，我下来再仔细琢磨琢磨。