如何利用子结构编号列重新编号结构编号列的序列值

作为常年泡在Stack Overflow的老玩家，我结合你的需求整理了一套可落地的SQL解决方案，适用于大多数支持窗口函数的数据库（比如PostgreSQL、SQL Server、MySQL 8+等）：

需求明确 #

每个PropertyId下的StructureNumber从1开始按顺序编号；结构存在子结构时，SubStructureNumber>0且与父级（顶层）结构的StructureNumber相同；顶层结构的SubStructureNumber为0，其子结构从1开始按顺序编号。

示例参考数据 #

先放一组符合规则的示例数据，帮你直观理解预期效果：

具体实现代码 #

我们通过CTE（公共表表达式）+窗口函数来分别处理顶层结构和子结构的编号逻辑：

WITH top_level_structures AS (
    SELECT 
        PropertyId,
        StructureNumber,
        -- 给每个PropertyId下的顶层结构分配递增编号
        ROW_NUMBER() OVER (PARTITION BY PropertyId ORDER BY CreateTime) AS new_structure_num
    FROM your_table
    WHERE SubStructureNumber = 0
)
SELECT 
    t.PropertyId,
    -- 子结构直接继承父级的StructureNumber，顶层结构用新生成的编号
    CASE 
        WHEN t.SubStructureNumber = 0 THEN ts.new_structure_num
        ELSE (SELECT new_structure_num FROM top_level_structures ts2 WHERE ts2.PropertyId = t.PropertyId AND ts2.StructureNumber = t.StructureNumber)
    END AS StructureNumber,
    -- 子结构按「PropertyId+StructureNumber」分组后生成递增编号
    CASE 
        WHEN t.SubStructureNumber = 0 THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY t.PropertyId, t.StructureNumber ORDER BY CreateTime)
    END AS SubStructureNumber
FROM your_table t
LEFT JOIN top_level_structures ts ON t.PropertyId = ts.PropertyId AND t.StructureNumber = ts.StructureNumber
ORDER BY t.PropertyId, StructureNumber, SubStructureNumber;

关键注意点 #

• 把your_table替换成你的实际表名
• CreateTime是用来确定结构顺序的字段，你可以换成业务上的排序依据（比如结构名称、ID等）
• 如果你的表没有用StructureNumber关联父子结构，而是用ParentStructureId这类字段，只需要调整子结构获取父级编号的逻辑即可

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