Skip to main content

SQLite ROW_NUMBER()窗口函数的实例

SQLite ROW_NUMBER()函数简介

ROW_NUMBER()是一个窗口函数,它为查询结果集的每一行指定一个连续整数。根据窗口定义中order by子句指定的顺序,从一行开始对行进行排序。

下面显示了ROW_NUMBER()函数的语法:

ROW_NUMBER() OVER (
[PARTITION BY expression1, expression2,...]
ORDER BY expression1 [ASC | DESC], expression2,...
)

在这种语法中,

SQLite ROW_NUMBER()示例

我们将使用示例数据库中的客户和发票表进行演示。

使用SQLite ROW_NUMBER()和ORDER BY子句示例

下面的语句返回所有客户的名字、姓氏和国家/地区。此外,它还使用ROW_NUMBER()函数向每个客户记录添加一个连续整数。

SELECT
ROW_NUMBER () OVER (
ORDER BY Country
) RowNum,
FirstName,
LastName,
country
FROM
customers;

以下是部分输出:

SQLite ROW_NUMBER with ORDER BY example

通过示例将SQLite ROW_NUMBER()与分区一起使用

以下语句为每个客户分配一个连续整数,并在客户所在国家/地区发生变化时重置数字:

SELECT
ROW_NUMBER () OVER (
PARTITION BY Country
ORDER BY FirstName
) RowNum,
FirstName,
LastName,
country
FROM
customers;

下图显示了部分输出:

SQLite ROW_NUMBER with PARTITION BY example

在本例中:

使用SQLite ROW_NUMBER()进行分页

ROW_NUMBER()函数可用于分页。例如,如果您想在一个表上以每页10行的方式逐页显示客户信息。

下面的语句返回第21到30行的客户信息,这是第三页,每页10行:

SELECT * FROM (
SELECT
ROW_NUMBER () OVER (
ORDER BY FirstName
) RowNum,
FirstName,
LastName,
Country
FROM
customers
) t
WHERE
RowNum > 20 AND RowNum <= 30

以下是输出:

SQLite ROW_NUMBER Pagination example

在本例中:

使用SQL ROW_NUMBER()查找每个组的第n个最大值

下面的语句创建了一个名为Sales的新视图,该视图由客户id、名字、姓氏、国家和金额组成。从发票表中检索金额:

CREATE VIEW Sales 
AS
SELECT
CustomerId,
FirstName,
LastName,
Country,
SUM( total ) Amount
FROM
invoices
INNER JOIN customers USING (CustomerId)
GROUP BY
CustomerId;

以下查询从Sales视图返回数据:

SELECT
*
FROM
sales;

SQLite ROW_NUMBER sales view

下表列出了每个国家拥有最高金额的客户:

SELECT 
Country,
FirstName,
LastName,
Amount
FROM (
SELECT
Country,
FirstName,
LastName,
Amount,
ROW_NUMBER() OVER (
PARTITION BY country
ORDER BY Amount DESC
) RowNum
FROM
Sales )
WHERE
RowNum = 1;

结果如下:

SQLite ROW_NUMBER nth highest value per group example

在子查询中:

外部查询选择RowNum值为1的客户。

如果您将WHERE子句中的行号更改为2、3等,您将得到金额第二高、金额第三高的客户,等等。

在本教程中,您学习了如何使用SQLite ROW_NUMBER()函数为查询结果集中的每一行指定一个连续整数。