create table dgtest
(
ID int,
ParentID int,
Name nvarchar(20)
)
insert dgtest
select 1,0,‘江苏省’
union all
select 2,1,‘苏州市’
union all
select 3,2,‘高新区’
union all
select 4,3,‘东渚镇’
union all
select 5,0,‘浙江省’
union all
select 6,5,‘杭州市’
union all
select 7,6,‘萧山区’
select * from dgtest
;with mytab
as
(
select * from dgtest where ID=4
union all
select bb.* from dgtest bb inner join mytab aa on bb.ID = aa.ParentID
)
select * from mytab
如果with前边有查询语句 需要加; 如果没有查询语句不需要