SQL如何按item字段分组生成递增序号并更新对应no字段

适用支持窗口函数的主流数据库（MySQL 8.0+/PostgreSQL/SQL Server/Oracle） #

使用ROW_NUMBER()窗口函数即可实现按item分组的独立递增序号，若要更新原表的no字段，写法如下：

UPDATE 你的实际表名 t1
INNER JOIN (
    SELECT 
        表主键ID, -- 替换为你表内的唯一主键字段名
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY 表主键ID) AS new_no
    FROM 你的实际表名
) t2 ON t1.表主键ID = t2.表主键ID
SET t1.no = t2.new_no;

注意：ORDER BY后的字段可根据业务需求调整，比如要按记录插入时间排序就替换为对应时间字段，只要能确定组内的排序规则即可。

如果仅需要查询时生成序号，不需要修改原表，可直接用以下语句：

SELECT 
    item,
    ROW_NUMBER() OVER (PARTITION BY item ORDER BY 表主键ID) AS no
FROM 你的实际表名;

适用不支持窗口函数的低版本数据库（如MySQL 5.x） #

可通过用户变量实现分组计数逻辑：

UPDATE 你的实际表名 t1
INNER JOIN (
    SELECT 
        表主键ID,
        @row_num := IF(@current_item = item, @row_num + 1, 1) AS new_no,
        @current_item := item
    FROM 你的实际表名
    ORDER BY item, 表主键ID
) t2 ON t1.表主键ID = t2.表主键ID,
(SELECT @row_num := 0, @current_item := NULL) AS init
SET t1.no = t2.new_no;

内容的提问来源于stack exchange，提问作者grn.developer