sql server递归查询

如下图，查询每个节点之间的关系，以及层级 表中存储的数据如下： 数据类型：

1、查询每个节点之间的关系，以及层级

逻辑： 该表自连接，son_id 与 father_id 相等，一步一步连接下去

WITH cte(son_id, father_id, ReportPath, Level) AS
(
    SELECT son_id, father_id, CAST(son_id + '->' + father_id AS NVARCHAR(max)) AS ReportPath,2 AS Level FROM test where father_id = 0
    UNION ALL
    SELECT c.son_id, c.father_id, c.son_id+ '->'+ P.ReportPath AS ReportPath , p.Level+1 AS Level
    FROM cte P INNER JOIN test c ON P.son_id = c.father_id
    
)
SELECT *  FROM cte ORDER BY son_id;

2、不限制 where father_id = 0 这个起始条件

则会出现每一层的关系，然后用son_id,father_id 分组后取最大的层级即可

WITH cte(son_id, father_id, ReportPath, Level) AS
(
    SELECT son_id, father_id, CAST(son_id + '->' + father_id AS NVARCHAR(max)) AS ReportPath,2 AS Level FROM test 
    UNION ALL
    SELECT c.son_id, c.father_id, c.son_id+ '->'+ P.ReportPath AS ReportPath , p.Level+1 AS Level
    FROM cte P INNER JOIN test c ON P.son_id = c.father_id
    
)
SELECT * into test_tree FROM cte ORDER BY son_id;

select * from (
select * ,DENSE_RANK( )over(partition by son_id,father_id order by Level desc) as rn from test_tree)t
where rn =1

排序后获取结果与有限制条件的结果一致。