--利用cte实现递归
--> 测试数据:[tb]
DECLARE @tb Table([id] int,[col1] varchar(8),[col2] int)
insert @tb
select 1,'河北省',0 union all
select 2,'邢台市',1 union all
select 3,'石家庄市',1 union all
select 4,'张家口市',1 union all
select 5,'南宫',2 union all
select 6,'坝上',4 union all
select 7,'任县',2 union all
select 8,'清河',2 union all
select 9,'河南省',0 union all
select 10,'新乡市',9 union all
select 11,'aaa',10 union all
select 12,'bbb',10
;with t as(
select * from @tb where col1='河北省'
union all
select a.* from @tb a ,t where a.col2=t.id
)
select * from t