SQL 递归查询
直接上代码
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;
鹰击长空,鱼翔浅底