如何编写SQL查询在SELECT子句中添加item分组内的排序序号

你这个需求是典型的分组内排序编号场景，直接用窗口函数就能实现。

原始测试数据 #

预期输出 #

新增order字段为相同item分组下，按order id升序排列的连续序号：

标准SQL实现（支持MySQL8.0+、PostgreSQL、SQL Server等主流数据库） #

假设你的数据表名为order_info，直接执行以下查询即可：

SELECT
  `order id`,
  item,
  ROW_NUMBER() OVER (
    PARTITION BY item 
    ORDER BY `order id`
  ) AS `order`
FROM order_info
-- 最后排序是为了让同item的数据集中展示，和你给出的结果顺序一致
ORDER BY item, `order id`;

语法说明 #

• PARTITION BY item 指定分组维度，不同item的序号独立计算
• ORDER BY order id`` 指定分组内的排序规则，按订单id从小到大编号
• 因为order是SQL保留关键字，作为字段别名时必须用反引号包裹，否则会报语法错误

旧版MySQL 5.x 兼容写法 #

如果你的数据库版本不支持窗口函数，可以用用户变量模拟实现相同效果：

SELECT
  `order id`,
  item,
  @row_num := IF(@current_item = item, @row_num + 1, 1) AS `order`,
  @current_item := item
FROM order_info, (SELECT @current_item := NULL, @row_num := 0) AS var_init
ORDER BY item, `order id`;

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