SQL递归有关问题

/**********************************测试题：已知如下一张表，它是某人的行程路线，请找出他的行程。要求：用sql来实现。得出路线形同：张山：A→B→C……**********************************/use mastergoif object_id('tempdb..temps')>0 drop table #temp   create table temps          ( sub_item  int not  null,            name  varchar(10) null,--            leave varchar(1) null, --            arrive varchar(1) null, --          )insert into temps (sub_item,name,leave,arrive)values(1,'张三','A','B' )insert into temps (sub_item,name,leave,arrive)values(2,'张三','D','E' )insert into temps (sub_item,name,leave,arrive)values(3,'张三','F','G' )insert into temps (sub_item,name,leave,arrive)values(4,'张三','C','D' )insert into temps (sub_item,name,leave,arrive)values(5,'张三','B','C' )insert into temps (sub_item,name,leave,arrive)values(6,'张三','E','F' )insert into temps (sub_item,name,leave,arrive)values(7,'李四','A','B' )insert into temps (sub_item,name,leave,arrive)                                   values(8,'李四','D','E' )insert into temps (sub_item,name,leave,arrive)values(9,'李四','C','D' )insert into temps (sub_item,name,leave,arrive)values(10,'李四','B','C' )insert into temps (sub_item,name,leave,arrive)values(11,'李四','E','F' )

;with cte AS (select name,arrive,path = CAST(leave +'→'+arrive AS VARCHAR(3000))from temps awhere not exists (  select 1 from temps  where arrive = a.leave  and name = a.name  )UNION ALLselect B.name,A.arrive,path = CAST(B.PATH +'→'+A.arrive AS VARCHAR(3000))from temps a,CTE Bwhere A.leave = B.arriveAND A.NAME=B.NAME)SELECT NAME,PATH FROM CTE AWHERE NOT EXISTS (SELECT 1 FROM CTEWHERE NAME = A.NAMEAND LEN(PATH)>LEN(A.PATH))--结果NAME    PATH李四    A→B→C→D→E→F张三    A→B→C→D→E→F→G
------解决方案--------------------
LEN(PATH)判断不太合理，修改如下

SQL code
;with cte AS (select name,arrive,path = CAST(leave +'→'+arrive AS VARCHAR(3000)),LEV=1from temps awhere not exists (  select 1 from temps  where arrive = a.leave  and name = a.name  )UNION ALLselect B.name,A.arrive,path = CAST(B.PATH +'→'+A.arrive AS VARCHAR(3000)),LEV = B.LEV + 1from temps a,CTE Bwhere A.leave = B.arriveAND A.NAME=B.NAME)SELECT NAME,PATH FROM CTE AWHERE NOT EXISTS (SELECT 1 FROM CTEWHERE NAME = A.NAMEAND LEV>A.LEV)
------解决方案--------------------
SQL code
with tba as(select name,leave,arrive from temps group by name,leave,arrive)select distinct name,路线=STuff((select '-'+LEAVE from tba c where c.name=b.name for xml path('')),1,1,'')from tba b-----------李四    A-B-C-D-E张三    A-B-C-D-E-F