SQL Server中ROW_NUMBER()函数的使用

SQL Server中ROW_NUMBER()函数的使用

ROW_NUMBER()返回结果集分区内行的序列号，每个分区的第一行从1开始。

先创建学生成绩表和班级信息表并插入测试数据，用于以下实例的使用。

/-创建学生成绩表-/
CREATE TABLE StuScore
(
StuID INT, --学生编号
StuName VARCHAR(20), --学生姓名
ClassID INT, --班级编号
Subject VARCHAR(20), --科目
Score INT --成绩
);

/-创建班级信息表-/
CREATE TABLE ClassInfo
(
ClassID INT, --班级编号
ClassName VARCHAR(20), --班级名称
);

/-添加学生成绩信息-/
INSERT INTO StuScore VALUES(1,‘张三’,1,‘语文’,60);
INSERT INTO StuScore VALUES(1,‘张三’,1,‘数学’,90);
INSERT INTO StuScore VALUES(1,‘张三’,1,‘英语’,70);
INSERT INTO StuScore VALUES(2,‘李四’,2,‘语文’,100);
INSERT INTO StuScore VALUES(2,‘李四’,2,‘数学’,90);
INSERT INTO StuScore VALUES(3,‘王五’,2,‘语文’,80);

/-添加班级信息-/
INSERT INTO ClassInfo VALUES(1,‘高一（1）班’);
INSERT INTO ClassInfo VALUES(2,‘高二（3）班’);
1、ROW_NUMBER() OVER(ORDER BY)

ROW_NUMBER() OVER(ORDER BY 字段1 DESC)是先把字段1降序，再为降序以后的每条记录返回一个序号。

【实例】使用ROW_NUMBER() OVER(ORDER BY)按学生成绩排序，并为每条排序后的记录返回一个序号。

SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Row_Index,*
FROM StuScore
执行结果：

Row_Index StuID StuName ClassID Subject Score

---

1 2 李四 2 语文 100
2 2 李四 2 数学 90
3 1 张三 1 数学 90
4 3 王五 2 语文 80
5 1 张三 1 英语 70
6 1 张三 1 语文 60

2、ROW_NUMBER() OVER(PARTITION BY)

ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2 DESC)表示根据字段1分组，在分组内部根据字段2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。

【实例】使用ROW_NUMBER() OVER(PARTITION BY)按学生编号分组且按学生成绩排序，再为每条排序后的记录返回一个序号。

SELECT ROW_NUMBER() OVER(PARTITION BY StuID ORDER BY Score DESC) AS Row_Index,*
FROM StuScore
执行结果：

Row_Index StuID StuName ClassID Subject Score

---

1 1 张三 1 数学 90
2 1 张三 1 英语 70
3 1 张三 1 语文 60
1 2 李四 2 语文 100
2 2 李四 2 数学 90
1 3 王五 2 语文 80

【实例】查询每个学生最高的成绩。

SELECT t.* FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY StuID ORDER BY Score DESC) AS Row_Index,*
FROM StuScore
) t WHERE t.Row_Index = 1
执行结果：

Row_Index StuID StuName ClassID Subject Score

---

1 1 张三 1 数学 90
1 2 李四 2 语文 100
1 3 王五 2 语文 80

【实例】查询每个学生最高的成绩，并关联班级名称。

SELECT t1.StuID,t1.StuName,t1.Subject,t1.Score,t2.ClassName FROM(
SELECT tt.* FROM(
SELECT ROW_NUMBER() OVER(PARTITION BY StuID ORDER BY Score DESC) AS Row_Index,*
FROM StuScore
) tt WHERE tt.Row_Index = 1) t1
LEFT JOIN ClassInfo t2 ON t1.ClassID = t2.ClassID
执行结果：

StuID StuName Subject Score ClassName

---

1 张三 数学 90 高一（1）班
2 李四 语文 100 高二（3）班
3 王五 语文 80 高二（3）班

作者：pan_junbiao
来源：优快云
原文：https://blog.youkuaiyun.com/pan_junbiao/article/details/79941162
版权声明：本文为博主原创文章，转载请附上博文链接！