SQL Server 2005 CTE的用法

if object_id('[tb]') is not null

drop table [tb] 

go

create table [tb]([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