如何对分组子集实现ORDER_VALUE自动递增编号？

需求说明 #

现有包含四列（ORDER_BASIS、GROUP_1、GROUP_2、ORDER_VALUE）的表格，前三列数据已填充，需为ORDER_VALUE列填充整数：每个GROUP_1与GROUP_2的组合子集内，按ORDER_BASIS的值从小到大排序，从1开始依次编号。

原始数据 #

预期结果 #

解决方案 #

使用SQL窗口函数ROW_NUMBER()即可实现该需求，它支持在指定分组内按指定字段排序并生成递增编号。

1. 查询生成排序值（标准SQL，适用于PostgreSQL、SQL Server、Oracle 12c+、MySQL 8.0+等） #

SELECT 
    ORDER_BASIS,
    GROUP_1,
    GROUP_2,
    ROW_NUMBER() OVER (PARTITION BY GROUP_1, GROUP_2 ORDER BY ORDER_BASIS) AS ORDER_VALUE
FROM your_table_name;

• PARTITION BY GROUP_1, GROUP_2：指定以这两列的组合作为分组依据
• ORDER BY ORDER_BASIS：指定分组内按该列的值从小到大排序
• ROW_NUMBER()：为每组内的行生成从1开始的连续整数

2. 更新原表的ORDER_VALUE列 #

SQL Server 版本

WITH ranked_data AS (
    SELECT 
        ORDER_VALUE,
        ROW_NUMBER() OVER (PARTITION BY GROUP_1, GROUP_2 ORDER BY ORDER_BASIS) AS new_order_value
    FROM your_table_name
)
UPDATE ranked_data
SET ORDER_VALUE = new_order_value;

MySQL 8.0+ 版本

UPDATE your_table_name t1
JOIN (
    SELECT 
        ORDER_BASIS, GROUP_1, GROUP_2,
        ROW_NUMBER() OVER (PARTITION BY GROUP_1, GROUP_2 ORDER BY ORDER_BASIS) AS new_order_value
    FROM your_table_name
) t2
ON t1.ORDER_BASIS = t2.ORDER_BASIS 
   AND t1.GROUP_1 = t2.GROUP_1 
   AND t1.GROUP_2 = t2.GROUP_2
SET t1.ORDER_VALUE = t2.new_order_value;

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