Microsoft SQL Server层级式自定义行号实现求助

我明白你想要实现这种层级式的自定义行号，这种需求在处理分级数据时特别常见。你之前用变量的写法没达到预期，主要是因为SQL Server中SELECT语句里直接修改变量的行为不可预测，而且没有正确处理二级节点和对应一级节点的关联关系。

下面提供两种可靠的实现方案，你可以根据自己的表结构选择：

方案1：基于行顺序的层级编号（适合无父ID的场景） #

如果你的数据是按“一级节点→二级节点”的顺序排列的，且有确定的排序字段（比如sort_order），可以用窗口函数来累计主编号并生成子编号：

WITH ranked_data AS (
    SELECT 
        col1,
        CHART_LEVEL,
        -- 累计计算当前行及之前的一级节点数量，作为主编号
        SUM(CASE WHEN CHART_LEVEL = 1 THEN 1 ELSE 0 END) OVER (
            ORDER BY sort_order 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS main_no,
        -- 在同一个主编号分组内，生成二级节点的序号
        ROW_NUMBER() OVER (
            PARTITION BY SUM(CASE WHEN CHART_LEVEL = 1 THEN 1 ELSE 0 END) OVER (
                ORDER BY sort_order 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )
            ORDER BY sort_order
        ) AS sub_no
    FROM myTable
)
SELECT 
    col1,
    CASE 
        WHEN CHART_LEVEL = 1 THEN CONCAT(main_no, '.')
        WHEN CHART_LEVEL = 2 THEN CONCAT(main_no, '.', sub_no)
        ELSE '' -- 其他层级可按需调整
    END AS RowNo
FROM ranked_data
ORDER BY sort_order;

方案2：基于父ID的层级编号（适合有明确父子关联的场景） #

如果你的表有parent_id字段（二级节点通过该字段关联到对应的一级节点），这种方案更准确，不依赖行的顺序：

-- 先给所有一级节点分配主编号
WITH main_nodes AS (
    SELECT 
        id,
        col1,
        ROW_NUMBER() OVER (ORDER BY sort_order) AS main_no
    FROM myTable
    WHERE CHART_LEVEL = 1
),
-- 给二级节点分配对应的主编号和子编号
sub_nodes AS (
    SELECT 
        m.main_no,
        t.col1,
        ROW_NUMBER() OVER (PARTITION BY t.parent_id ORDER BY t.sort_order) AS sub_no
    FROM myTable t
    JOIN main_nodes m ON t.parent_id = m.id
    WHERE CHART_LEVEL = 2
)
-- 合并一级和二级节点的结果并排序
SELECT col1, CONCAT(main_no, '.') AS RowNo FROM main_nodes
UNION ALL
SELECT col1, CONCAT(main_no, '.', sub_no) AS RowNo FROM sub_nodes
ORDER BY RowNo;

为什么你的原代码有问题？ #

1. 变量更新行为不可靠：在SELECT语句中直接修改@no、@subNo这类变量，SQL Server不保证执行顺序，可能导致编号混乱。
2. 语法错误：CONCAT(@no,'.'@subNo += 1)里缺少逗号分隔符，而且不能在CONCAT函数内直接做变量自增操作。
3. 层级关联缺失：没有明确二级节点属于哪个一级节点，导致子编号无法正确对应到主编号。

注意：两种方案都依赖sort_order字段来确保编号的顺序一致性，如果你的表没有这个字段，需要用其他可排序的字段（比如创建时间、ID等）替代。

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