Select top N rows in each group on SQL Server

  • 方法1,通过光标遍历每个分组,返回的是数据集(每个分组为一个表)
declare cur cursor for select country from customers group by country
declare @country nvarchar(100)
 
open cur
fetch next from cur into @country
 
while @@FETCH_STATUS = 0
begin
    select top 3 * from customers where country = @country
    fetch next from cur into @country
end
 
close cur
deallocate cur
  • 方法2,使用PARTITION BY
SELECT *
FROM products a
JOIN
  (SELECT [productid],
          Row_number() OVER (PARTITION BY categoryid
                             ORDER BY unitprice) AS RowNumber
   FROM products) b ON a.productid = b.productid
WHERE b.rownumber = 1
  • 输出如下:
ProductID   ProductName                              UnitPrice             CategoryID  RowNumber
----------- ---------------------------------------- --------------------- ----------- --------------------
24          Guaraná Fantástica                       4.50                  1           1
3           Aniseed Syrup                            10.00                 2           1
19          Teatime Chocolate Biscuits               9.20                  3           1
33          Geitost                                  2.50                  4           1
52          Filo Mix                                 7.00                  5           1
54          Tourtière                                7.45                  6           1
74          Longlife Tofu                            10.00                 7           1
13          Konbu                                    6.00                  8           1
 
(8 rows affected)