2010-04-13 15:00

select * into t from

(

select 1 as id,'中国' as [name],0 as parentId union all

select 2,'江西',1 union all

select 3,'浙江',1 union all

select 4,'杭州',3 union all

select 5,'南昌',2 union all

select 6,'桐乡',4 union all

select 7,'桐炉',4 union all

select 8,'进贤',5 union all

select 9,'东湖区',5 union all

select 10,'建德',4

) as temp

--select * from t;

;

with tree(id,name,parentID) as--//括号内的内容也可以不要。相当于传递给方法的参数

(

select id,[name],parentID from t where [name] = '浙江'

union all

select t.id,t.[name],t.parentID from t inner join tree on t.parentID = tree.id

)

select * from tree

drop table t

参考：