SqlServer取每组中的TOP(n)方法实验

测试数据如下:
– 方法一,此方法速度最快
SELECT *
FROM ( SELECT DENSE_RANK() OVER(PARTITION BY a ORDER BY b DESC) AS RNK,
a,
b
FROM [dbo].[t]
) zz
WHERE RNK<3

– 方法二
SELECT *
FROM [dbo].[t] t1
WHERE b
IN (SELECT DISTINCT TOP(2) b FROM [dbo].[t] t2 WHERE t1.a= t2.a ORDER BY b DESC)

– 方法三
SELECT *
FROM (SELECT DISTINCT a FROM [dbo].[t]) t1
INNER JOIN [dbo].[t] t2
ON t2.b IN (SELECT DISTINCT TOP(2) b FROM [dbo].[t] t3 WHERE t3.a= t1.a ORDER BY b DESC) AND t2.a=t1.a

– 方法四
SELECT t1.a,zz.b
FROM [dbo].[t] t1
CROSS APPLY (SELECT DISTINCT TOP(2) b FROM [dbo].[t] t2 WHERE t1.a=t2.a ORDER BY b DESC) zz
WHERE t1.b=zz.b