在 SQLite 中，ROW_NUMBER() 是一个窗口函数，用于为结果集中的每一行分配一个唯一的行号，行号按照指定的排序顺序依次递增。以下是关于它的详细使用方法和示例。

基本语法

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

• PARTITION BY：可选子句，用于将结果集划分为多个分区。在每个分区内，ROW_NUMBER() 会重新开始编号。partition_expression 是用于分区的列或表达式。
• ORDER BY：必需子句，用于指定行的排序顺序。sort_expression 是用于排序的列或表达式，ASC 表示升序（默认），DESC 表示降序。

使用示例

1. 不使用PARTITION BY子句

假设我们有一个 employees 表，包含 employee_id 和 salary 两列，我们要为所有员工按照工资升序分配行号。

-- 创建示例表
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    salary REAL
);

-- 插入示例数据
INSERT INTO employees (salary) VALUES (5000);
INSERT INTO employees (salary) VALUES (3000);
INSERT INTO employees (salary) VALUES (4000);

-- 使用 ROW_NUMBER() 函数
SELECT 
    employee_id,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS row_num
FROM 
    employees;

在这个示例中，ROW_NUMBER() 函数会为整个结果集按照 salary 列的升序分配行号。结果可能如下：

2. 使用PARTITION BY子句

假设我们的 employees 表新增了一个 department 列，我们要为每个部门内的员工按照工资升序分配行号。

-- 修改表结构并插入新数据
ALTER TABLE employees ADD COLUMN department TEXT;
UPDATE employees SET department = 'HR' WHERE employee_id = 1;
UPDATE employees SET department = 'IT' WHERE employee_id = 2;
UPDATE employees SET department = 'IT' WHERE employee_id = 3;
INSERT INTO employees (salary, department) VALUES (6000, 'HR');

-- 使用 ROW_NUMBER() 函数并进行分区
SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS row_num
FROM 
    employees;

在这个示例中，PARTITION BY department 将结果集按部门进行分区，在每个部门内，ROW_NUMBER() 函数会根据 salary 列的升序重新分配行号。结果可能如下：

注意事项

• 排序顺序的重要性：ORDER BY 子句决定了行号的分配顺序，如果不指定合适的排序规则，行号可能不符合预期。
• 性能考虑：在处理大数据集时，使用 ROW_NUMBER() 函数可能会影响性能，特别是当使用复杂的 PARTITION BY 和 ORDER BY 子句时。
• 唯一性：ROW_NUMBER() 函数为每一行分配的行号是唯一的，即使某些行在排序键上的值相同，它们的行号也会不同。