IF OBJECT_ID(‘tb‘) IS NOT NULL   DROP TABLE tbcreate table tb(id varchar(3) , pid varchar(3) , name varchar(10))insert into tb values(‘001‘ , null  , ‘广东省‘)insert into tb values(‘002‘ , ‘001‘ , ‘广州市‘)insert into tb values(‘003‘ , ‘001‘ , ‘深圳市‘)insert into tb values(‘004‘ , ‘002‘ , ‘天河区‘)insert into tb values(‘005‘ , ‘003‘ , ‘罗湖区‘)insert into tb values(‘006‘ , ‘003‘ , ‘福田区‘)insert into tb values(‘007‘ , ‘003‘ , ‘宝安区‘)insert into tb values(‘008‘ , ‘007‘ , ‘西乡镇‘)insert into tb values(‘009‘ , ‘007‘ , ‘龙华镇‘)insert into tb values(‘010‘ , ‘007‘ , ‘松岗镇‘)GOSELECT * FROM tb AS tDECLARE @ID VARCHAR(3) --查询ID = ‘001‘的所有子节点SET @ID = ‘007‘;WITH Temp AS (  SELECT id,pid,NAME FROM tb AS t WHERE t.id=@ID  UNION ALL  SELECT t2.id,t2.pid,t2.NAME FROM tb t2 INNER JOIN Temp tm on t2.pid=tm.ID)SELECT * FROM Temp AS t WHERE id!=@ID