数据库递归查询

CREATE TABLE [dbo].[City](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [city_name] [nvarchar](100) NOT NULL,
    [parent_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

插入数据

1，根据节点id获取所有的父节点

with CityTree as
(
SELECT id,parent_id,city_name from City c where c.id = 2
union all
select b.id,b.parent_id,b.city_name from City b join CityTree t 
ON t.parent_id = b.id
)
Select * from CityTree

结果

 

2，根据节点id获取所有的子节点

with CityTree as
(
SELECT id,parent_id,city_name from City c where c.id =2
union all
select b.id,b.parent_id,b.city_name from City b join CityTree t 
ON t.id = b.parent_id
)
Select * from CityTree

结果

3，根据节点ID获取下级节点以及下下级节点

with cte as
(
    select id,parent_id,City_Name, 0 as lvl from City
    where id = 2
    union all
    select d.id,d.parent_id,d.City_Name,lvl + 1 from cte c inner join City d
    on c.Id = d.parent_id where lvl < 2
)
select * from cte 

结果