来源：https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-row_number/

版本要求

来源：https://stackoverflow.com/questions/16847574/how-to-use-row-number-in-sqlite

2018-09-15 (3.25.0)

语法

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

通过 ORDER BY 获取行号

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

通过分组和 ORDER BY 获取行号

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

分页

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

获取每组的最高值

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

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;