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

参考：http://www.cnitblog.com/seeyeah/archive/2009/03/25/55749.html