如何用SQL为按Desc和Proj_Id分组的记录分配Master_Line_Num

嘿，这个需求其实用窗口函数就能轻松搞定！你要的是按Desc和Proj_Id分组，给每个分组内按年份排序的记录分配连续的Master_Line_Num，这正好是ROW_NUMBER()窗口函数的拿手好戏。

核心查询语句 #

SELECT 
    ID,
    `Desc`, -- 注意：Desc是SQL关键字，用反引号包裹避免语法错误
    Proj_Id,
    Year,
    ROW_NUMBER() OVER (PARTITION BY `Desc`, Proj_Id ORDER BY Year ASC) AS Master_Line_Num
FROM 
    your_table_name; -- 替换成你的实际表名

语句细节解释 #

• ROW_NUMBER()：为每一行生成唯一的连续整数编号，同一分组内会从1开始递增
• PARTITION BY Desc, Proj_Id：指定分组规则，把Desc和Proj_Id完全相同的记录归为一组
• ORDER BY Year ASC：确保每个分组内的记录按年份升序排列，这样编号会严格跟着年份顺序走

针对你的样本数据的验证结果 #

运行上述语句后，你的样本数据会得到完全符合预期的结果：

如果需要更新原表的Master_Line_Num字段 #

如果要把计算好的编号直接写入原表，不同数据库的语法略有差异，这里给两个主流数据库的示例：

PostgreSQL/SQL Server 更新示例 #

WITH numbered_rows AS (
    SELECT 
        ID,
        ROW_NUMBER() OVER (PARTITION BY `Desc`, Proj_Id ORDER BY Year ASC) AS new_num
    FROM your_table_name
)
UPDATE your_table_name
SET Master_Line_Num = new_num
FROM numbered_rows
WHERE your_table_name.ID = numbered_rows.ID;

MySQL 更新示例 #

UPDATE your_table_name t1
JOIN (
    SELECT 
        ID,
        ROW_NUMBER() OVER (PARTITION BY `Desc`, Proj_Id ORDER BY Year ASC) AS new_num
    FROM your_table_name
) t2 ON t1.ID = t2.ID
SET t1.Master_Line_Num = t2.new_num;

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