SQL Server中ROW_NUMBER()函数的使用

墨蓝 2021-09-15 05:48 367阅读 0赞

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）班