您的位置:

SQL Server ROW_NUMBER 函数详解

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 函数是非常实用的窗口函数,常用于查询某张表格中的前几条数据、数据分页等操作。除此之外,还可以通过高级用法快速查询分组数据中的首行、随机排序等。