MSSQL 按照某一列分组后获取前几条记录

MSSQL 按照某一列分组后去取几条记录

背景
记录一下在日常处理后台数据时遇到的问题，先简单描述一下需求
因为客户操作失败【也可以说是产品问题】，导致的后台多了很多数据，需要先查看数据看下判断，过多的需求不浪费时间在此赘述了，我们直接进入正题

---

先上模板

Method 1 方法1

① SELECT 要输出的列，除nn外 FROM
(SELECT nn=ROW_NUMBER() OVER(PARTITION BY 分组的列 ORDER BY 排序的列),* FROM 表名) b WHERE nn<=前N条数据
如果有小伙伴不是很明白可以先这样处理
②. select * from （select rownum=ROW_NUMBER() OVER(PARTITON BY 想要分组的列 ORDER BY 要排序的列 ),* FROM 表名）T where rownum< 每个分组想要的条数

我们先将模板弄懂了，实际应用的时候按照我们的实际需求也就是手到擒来。
为什么还要赘述②的表述呢，是为了让大家更好的理解，第一种方法中我们用到了ROW_NUMBER（）函数，该函数是在SQL 2005及以上版本才有的，如果数据库是05版本以下的话，我们可以使用另外的方法，在此先按下不表。

1. rownum 就是按照分组条件和排序的选择对于每一个分组内的内容做的行标号 ，我们在最外层的使用的时候* 输出的时候，可以帮助我们更好的理解，rownum的下标从1开始
2. PARTITON BY 想要分组的列 ，就是我们作为分组的条件列 后边跟我们需要排序的列，rownum=ROW_NUMBER() OVER(PARTITON BY 想要分组的列 ORDER BY 要排序的列 ）这一句只是为了根据我们需求生成对应的rownum，后边的* 是我们需要选择的表的所有字段，我们也可以在此选择我们想要显示的列。
3. 最后的条件就是我们想要显示的前几条这样的条件啦

Method 2 方法2

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

如果我们把方法1看做是先构建一个对于分组的每个组新增一个组内ID，那方法2 就是在查询条件进行处理在分组
关键点 就是内部的条件匹配

Method 3 方法3

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

该方法也是构造一个子表在子表中在进行处理，类似于方法一和方法二的结合

---

实操一下

---

建表并插入数据

---

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

---

建表
CREATE TABLE Test(
ID bigint IDENTITY(1,1),
Name nvarchar(50),
CourseName nvarchar(50),
Score int )

插入数据
INSERT into Test.dbo.Test(Name,CourseName,Score)
Values(‘张三’,‘物理’,88),(‘李四’,‘物理’,98),(‘王五’,‘物理’,86),
(‘张三’,‘英语’,92),(‘李四’,‘英语’,88),(‘王五’,‘英语’,86)

---

表数据如下

我们显示一下按照学科成绩排名前三位的学生

方法1

SELECT * FROM
(SELECT rownum=ROW_NUMBER() OVER(PARTITION BY CourseName ORDER BY Score desc),* FROM Test) tt 
WHERE rownum<4

方法二
SELECT * FROM Test tt where 
(SELECT COUNT(*) FROM Test WHERE CourseName =tt.CourseName AND ID<=tt.ID )<=3 ORDER BY CourseName ,Score desc  

方法三
SELECT * FROM Test TT
WHERE ID IN(SELECT DISTINCT TOP 3 ID FROM Test WHERE CourseName=TT.CourseName)
ORDER BY CourseName ,Score DESC

小伙伴去试试吧
有问题可以留下您的评论，看到就会回复啦