SQL如何基于指定字段分组从1开始生成自增序号计算字段

你原有SQL的问题：SELECT id, SUM(COUNT(id)) AS result FROM t1 GROUP BY id用了GROUP BY id会把相同id的所有行合并为一行，只能得到每个id的聚合结果，无法输出每组内的多条记录和逐行自增序号，不符合需求。

推荐方案（支持MySQL 8.0+、PostgreSQL、SQL Server、Oracle等所有支持窗口函数的数据库） #

用ROW_NUMBER()窗口函数实现，代码如下：

SELECT
  id,
  ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS result
FROM t1;

• PARTITION BY id 作用是将所有记录按id字段分组，相同id的记录归为同一窗口
• ORDER BY id 用来定义同一分组内的序号排序规则，如果你需要按其他字段（比如记录插入时间、业务字段）排序生成序号，替换为对应字段即可。

兼容老版本MySQL（5.x及以下不支持窗口函数的场景） #

可以借助用户变量实现：

SELECT
  id,
  CASE
    WHEN @pre_id = id THEN @row_num := @row_num + 1
    ELSE @row_num := 1 AND @pre_id := id
  END AS result
FROM t1, (SELECT @pre_id := NULL, @row_num := 0) AS init_vars
ORDER BY id;

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