在SQL Server中展平随时间变化的层次结构并获取每对结构之间的距离

WITH RecursiveCTE AS (
    -- 初始查询，获取根节点
    SELECT id, name, parent_id, 0 AS level
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询，获取子节点
    SELECT t.id, t.name, t.parent_id, c.level + 1
    FROM your_table t
    INNER JOIN RecursiveCTE c ON t.parent_id = c.id
)
SELECT *
FROM RecursiveCTE
ORDER BY level, id;

WITH RecursiveCTE AS (
    -- 初始查询，获取根节点
    SELECT id, name, parent_id, 0 AS level
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归查询，获取子节点
    SELECT t.id, t.name, t.parent_id, c.level + 1
    FROM your_table t
    INNER JOIN RecursiveCTE c ON t.parent_id = c.id
), DistanceCTE AS (
    -- 计算每对结构之间的距离
    SELECT c1.id AS id1, c1.name AS name1, c2.id AS id2, c2.name AS name2, ABS(c1.level - c2.level) AS distance
    FROM RecursiveCTE c1
    CROSS JOIN RecursiveCTE c2
)
SELECT *
FROM DistanceCTE
ORDER BY id1, id2;