CREATE TABLE City
(
id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
city_name NVARCHAR(100) NOT NULL,
parent_id INT NOT NULL
)

INSERT INTo City(city_name,parent_id) VALUES('北京',0)
INSERT INTo City(city_name,parent_id) VALUES('昌平区',1)
INSERT INTo City(city_name,parent_id) VALUES('天通苑',2)
INSERT INTo City(city_name,parent_id) VALUES('天通苑1区',3)
INSERT INTo City(city_name,parent_id) VALUES('朝阳区',1)
INSERT INTo City(city_name,parent_id) VALUES('东城区',9)
INSERT INTo City(city_name,parent_id) VALUES('乱乱2',6)
-------------------------------------------------
--根据节点ID获取所有子节点
-------------------------------------------------
SELECT * FROM City;
WITH CityTree AS
(
SELECT * from City where parent_id = 0--需要查找的父节点
UNION ALL
SELECT City.* from Citytree
JOIN City on CityTree.id = City.parent_id
)
SELECT * FROM CityTree;
-------------------------------------------------
--根据节点ID获取所有父节点
-------------------------------------------------
SELECT * FROM City;
with CityTree AS
(
SELECT * from City where Id=4 --需要查找的子节点
UNION ALL
SELECT City.* from CityTree
JOIN City on CityTree.parent_id= City.Id
)
SELECT * from CityTree;