SQL Server ROW_NUMBER 函数是在 SELECT 语句中常用的窗口函数,用于对每行数据进行编号,通过该函数可以快速查询出排序后的前几条数据、分页查询等操作。本文将从多个方面对 SQL Server ROW_NUMBER 函数进行详细的阐述。
一、ROW_NUMBER 函数基本语法
SELECT ROW_NUMBER() OVER ( ORDER BY expression1 [ASC|DESC], expression2 [ASC|DESC], ... ) AS row_no, column1, column2, ... FROM table_name
其中,ROW_NUMBER() 是窗口函数, OVER 子句定义窗口,ORDER BY 子句指定排序方式,AS row_no 为新增的行号列的别名,column1, column2, ... 为需要查询的列。
二、ROW_NUMBER 函数基本使用
1. 查询表格中的前 N 行数据
在 ORDER BY 子句中指定需要排序的列和排序方式,通过 WHERE 条件限定查询结果数:
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY score DESC) row_no, name, score FROM student ) t WHERE row_no <= N
2. 分页查询
分页查询需要将查询结果按照指定方式排序后,根据页码和每页显示数据条数计算出需要返回的数据范围:
DECLARE @PageIndex INT = 1 -- 页码 DECLARE @PageSize INT = 10 -- 每页显示的数据条数 SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY score DESC) row_no, name, score FROM student ) t WHERE row_no BETWEEN (@PageIndex-1)*@PageSize+1 AND @PageIndex*@PageSize
三、ROW_NUMBER 函数高级使用
1. 按照分组查询时每组数据的行号
在 OVER 子句中添加 PARTITION BY 子句指定分组方式:
SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) row_no, name, score, class_id FROM student ) t WHERE row_no = 1
2. 查询某一行相对于整个结果集的行号
在 ORDER BY 子句中添加 UNION ALL 子句:
DECLARE @name VARCHAR(10)='Lucy' SELECT row_no FROM ( SELECT ROW_NUMBER() OVER (ORDER BY score DESC) row_no, name FROM student UNION ALL SELECT 0, @name ) t WHERE name = @name
3. 按照某列的相同值分组时对组内数据进行随机排序
在 ORDER BY 子句中添加 NEWID() 函数:
SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY NEWID()) row_no, name, score, class_id FROM student ) t WHERE row_no = 1
四、总结
SQL Server ROW_NUMBER 函数是非常实用的窗口函数,常用于查询某张表格中的前几条数据、数据分页等操作。除此之外,还可以通过高级用法快速查询分组数据中的首行、随机排序等。