sql语句获取分批排名数据_sqlserver 分批排名

场景：假设一个表有多家公司，每家公司属于一个分组，每家公司有主键，分组，名称，营业额等数据

问题：用一个sql获取每个分组营业额前10名的公司

思路：按分组模拟生成每个分组按金额排序的行号，取行号前10的数据

假设公司表定义：
company(id：主键ID, name：名称, type：分组, amt：金额)

select d.line, d.id, d.name, d.type, d.amt from (
select (
case when 
(select count(*) from company a where a.type = c.type and a.amt = c.amt) > 1
then 
(select count(*) from company a where a.type = c.type and a.amt > c.amt) 
+ 
(select count(*) from company a where a.type = c.type and a.amt = c.amt and a.id <= c.id)
else 
(select count(*) from company a where a.type = c.type and a.amt >= c.amt) end) line, name, type, amt
from company c
) d 
where d.line <= 10
order by d.type, d.amt desc