MSSQL 查询分组前N条记录

时间:2021-08-20 09:40:38

sql语句中,查询分组中前n条记录的sql语句如下

第一种方法

select * from consultingfeebill a
where n>(select count(*) from consultingfeebill where a.FundAccountType=FundAccountType
and TradeAmount>a.TradeAmount)
order by TradeAmount desc

第二种方法

select * from consultingfeebill a
 where TradeAmount in(
     select top n TradeAmount from consultingfeebill where FundAccountType=a.FundAccountType
    order by TradeAmount desc)