如何按category_id分组生成行的创建顺序列？性能与实现咨询

现有如下数据表：

需要查询所有数据行，新增一列item_order_by_category，表示每个category_id分组内的创建顺序（可按id或created_at排序），期望结果如下：

请问能否通过高性能的SQL查询实现该需求？还是需要新增关联表等额外信息？

完全可以通过高性能的SQL查询实现，不需要新增关联表或额外存储信息，使用窗口函数即可完成。

实现SQL语句 #

根据需求，按created_at（或id）在category_id分组内排序生成序号，SQL语句如下：

SELECT 
    id,
    category_id,
    item_id,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY created_at ASC) AS item_order_by_category,
    created_at
FROM 
    your_table_name;

如果想用id排序（因id递增顺序与created_at一致），可将ORDER BY created_at ASC替换为ORDER BY id ASC。

性能优化建议 #

• 窗口函数是MySQL 8.0+、PostgreSQL、SQL Server等现代关系型数据库的原生特性，执行效率优异。
• 若数据量较大，建议给category_id和排序字段（created_at或id）建立联合索引：

  CREATE INDEX idx_category_created ON your_table_name(category_id, created_at);
  

  索引可帮助数据库直接完成分组与排序，避免全表扫描，进一步提升查询性能。

内容的提问来源于stack exchange，提问作者pileup