MSSQL—按照某一列分组后取前N条记录

以前在开发的时候遇到过一个需求，就是要按照某一列进行分组后取前几条数据，今天又有同事碰到了，帮解决了之后顺便写一篇博客记录一下。

首先先建一个基础数据表，代码如下：

IF OBJECT_ID(N‘Test‘) IS NOT NULL
    BEGIN
        DROP TABLE Test
    END

CREATE TABLE Test(
ID bigint IDENTITY(1,1),
Name nvarchar(50),
Department nvarchar(50))
       

INSERT INTO Test(Name,Department)
VALUES(‘张三‘,‘行政‘),
(‘李四‘,‘运营‘),
(‘王五‘,‘行政‘),
(‘赵六‘,‘研发‘),
(‘钱七‘,‘工程‘),
(‘Amy‘,‘研发‘),
(‘Tomy‘,‘工程‘),
(‘Tony‘,‘研发‘),
(‘Tom‘,‘工程‘),
(‘Alice‘,‘行政‘),
(‘Mary‘,‘行政‘),
(‘Elaine‘,‘运营‘),
(‘Geno‘,‘行政‘),
(‘Gary‘,‘工程‘)
GO

建好后，这张表的数据如下：

现在的需求是按照Department列进行分组，按ID取每个部门前2条记录，只写一条SQL语句的话，分别可以用3种方法实现，代码如下：

SELECT ID,Name,Department FROM
(SELECT nn=ROW_NUMBER() OVER(PARTITION BY Department ORDER BY ID),*  FROM Test) b WHERE nn<=2

 
SELECT * FROM Test t WHERE
(SELECT COUNT(*) FROM Test WHERE Department=t.Department AND ID<=t.ID)<=2 ORDER BY Department

SELECT * FROM Test t
WHERE ID in (SELECT DISTINCT TOP 2 ID FROM Test WHERE Department=t.Department)
ORDER BY Department

第一种方法使用了ROW_NUMBER()函数，这个函数是在SQL 2005及以上版本才有的，所以如果数据库是2000的话只能用下面两种方法，运行之后得到的结果都一样，如下图：

总结，碰到此类需求可以直接用下面代码进行套用：

SELECT 要输出的列，除nn外 FROM
(SELECT nn=ROW_NUMBER() OVER(PARTITION BY 分组的列 ORDER BY 排序的列),*  FROM 表名) b WHERE nn<=前N条数据

 
SELECT * FROM 表名 t WHERE
(SELECT COUNT(*) FROM 表名 WHERE 分组的列=t.分组的列 AND 排序的列<=t.排序的列)<=前N条数据 ORDER BY 分组的列

SELECT * FROM 表名 t
WHERE 排序的列 in (SELECT DISTINCT TOP 前N条数据 排序的列 FROM 表名 WHERE 分组的列=t.分组的列)
ORDER BY 分组的列