sql递归

create table region
(
	id int primary key identity(1,1),
	name nvarchar(50),
	upperId int
)

insert into region(name) values('中国');
insert into region(name) values('美国');
insert into region(name,upperId) values('浙江',1);
insert into region(name,upperId) values('江苏',1);
insert into region(name,upperId) values('加州',2);
insert into region(name,upperId) values('嘉兴',3);
insert into region(name,upperId) values('嘉善',6);

select * from region;

with reg as 
( 
    select id,name,upperId from region where id=2
    union all 
    select r.id,r.name,r.upperId from reg
    inner join region r on reg.id = r.upperId
) 
select * from reg 

获取全路径

with reg as 
( 
    select ParameterID,Code,Name,UpperID
    ,cast(Code AS nvarchar(4000)) Path_Code
    ,cast(Name AS nvarchar(4000)) Path_Name 
	FROM SYS_Parameter_EN where Code='EvaluationOfItem'
    union all 
    select r.ParameterID,r.Code,r.Name,r.UpperID
	,cast((reg.Path_Code+'/'+r.Code) AS nvarchar(4000)) Path_Code
    ,cast((reg.Path_Name+'/'+r.Name) AS nvarchar(4000)) Path_Name from reg
    inner join SYS_Parameter_EN r on reg.ParameterID = r.UpperID
) 
select * from reg