SQL查询每个大类中销售次数最多的N个小类
SQL里面，有一个销售表，假设有这么4个字段（ID,名字，大类，小类），现在要查找每一个大类里面销售次数最多的2个小类。
SQL语句如下：
select * from(
select
名字,大类,小类,count(id) as countInt,
rank()over(partition by 大类 order by count(id) desc) as NORow
from Test
GROUP BY Name,yaolei,xiaolei) v
select
名字,大类,小类,count(id) as countInt,
rank()over(partition by 大类 order by count(id) desc) as NORow
from Test
GROUP BY Name,yaolei,xiaolei) v
where NORow<3
补充
使用交叉法也是可以的，但是缺陷是只能取小于等于2行
SELECT DISTINCT b.*
FROM Test AS a
CROSS APPLY
(
select top (2) * from (
select
名字,大类,小类,count(id) as countInt
from Test
GROUP BY 名字,大类,小类) v
where v.大类=a.大类
) AS b
