如何用SQL实现根据行号获取父行号？附数据表示例

假设我们有一张带层级关系的原始数据表：

Code ParentCode oItem
-----------------------------
A    null       Item 001
B    A          Item 002
C    A          Item 003
D    C          Item 004
E    B          Item 005

已经通过SQL给这张表添加了行号Rn，得到如下中间结果：

Rn Code ParentCode oItem
------------------------------------------
1  A    null       Item 001
2  B    A          Item 002
3  C    A          Item 003
4  D    C          Item 004
5  E    B          Item 005

现在想要查询得到包含Rn、Code、RnParent（对应ParentCode的行号）、ParentCode、oItem的结果集，最直接的方案是使用自连接（Self-Join），具体实现如下：

方法：利用自连接关联父行的行号 #

如果你的带行号的数据是临时生成的，可以用CTE（公共表表达式）来封装生成行号的逻辑，再做自连接：

WITH numbered_items AS (
    -- 生成带行号的数据集，排序规则根据你的实际需求调整，这里和原问题的Rn顺序一致
    SELECT 
        ROW_NUMBER() OVER (ORDER BY Code) AS Rn,
        Code,
        ParentCode,
        oItem
    FROM your_original_table_name -- 替换成你实际的表名
)
SELECT 
    ni.Rn,
    ni.Code,
    parent_ni.Rn AS RnParent,
    ni.ParentCode,
    ni.oItem
FROM numbered_items ni
-- 左连接保证父节点为空的行也能被保留
LEFT JOIN numbered_items parent_ni 
    ON ni.ParentCode = parent_ni.Code
ORDER BY ni.Rn;

如果已经有一张包含Rn字段的物理表（比如你已经执行过添加行号的操作并保存），可以直接用这张表做自连接：

SELECT 
    t1.Rn,
    t1.Code,
    t2.Rn AS RnParent,
    t1.ParentCode,
    t1.oItem
FROM your_table_with_rn t1
LEFT JOIN your_table_with_rn t2 
    ON t1.ParentCode = t2.Code
ORDER BY t1.Rn;

预期查询结果 #

执行上述SQL后，你会得到如下结果集：

Rn Code RnParent ParentCode oItem
------------------------------------------
1  A    null     null       Item 001
2  B    1        A          Item 002
3  C    1        A          Item 003
4  D    3        C          Item 004
5  E    2        B          Item 005

这里用LEFT JOIN而不是INNER JOIN的原因是，当某行的ParentCode为null时（比如第一行的A），INNER JOIN会过滤掉该行，而LEFT JOIN会保留它，此时RnParent会显示为null，符合我们的需求。

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