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（）函数为查询结果集中的每一行指定一个连续整数。