如何让SQLite中row_number()遇到不同值时重新从1开始计数？

你需要给每个plain_note_id分组内的记录生成独立递增的序号，之前的写法缺少分组逻辑，导致序号全局递增而非分组内重置。

正确SQL语句 #

select 
    plain_note_id, 
    id, 
    row_number() over (PARTITION BY plain_note_id ORDER BY id) as `order` 
from attachment 
order by plain_note_id, id;

核心逻辑说明 #

• PARTITION BY plain_note_id：将数据按plain_note_id拆分为独立分组，每个分组单独计算序号
• ORDER BY id：在每个分组内，按照id的顺序生成从1开始的递增序号

执行结果 #

plain_note_id   id  order
========================= 
5               1   1
5               2   2
5               8   3
9               3   1
9               5   2

错误原因分析 #

你之前的语句ROW_NUMBER() over (ORDER BY plain_note_id)仅指定了全局排序规则，没有分组逻辑，因此序号会从1连续递增到5，无法实现每个plain_note_id分组内序号从1重置的需求。

内容的提问来源于stack exchange，提问作者Cheok Yan Cheng