/**********************************
测试题：已知如下一张表，它是某人的行程路线，请找出他的行程。
要求：用sql来实现。得出路线形同：张山：A→B→C……

**********************************/
use master
go
if 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 a
where not exists (
  select 1 from temps
  where arrive = a.leave
  and name = a.name
  )
UNION ALL
select B.name,A.arrive,path = CAST(B.PATH +'→'+A.arrive AS VARCHAR(3000))
from temps a,CTE B
where A.leave = B.arrive
AND A.NAME=B.NAME
)
SELECT NAME,PATH FROM CTE A
WHERE NOT EXISTS (
SELECT 1 FROM CTE
WHERE NAME = A.NAME
AND 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=1
from temps a
where not exists (
  select 1 from temps
  where arrive = a.leave
  and name = a.name
  )
UNION ALL
select B.name,A.arrive,path = CAST(B.PATH +'→'+A.arrive AS VARCHAR(3000)),LEV = B.LEV + 1
from temps a,CTE B
where A.leave = B.arrive
AND A.NAME=B.NAME
)
SELECT NAME,PATH FROM CTE A
WHERE NOT EXISTS (
SELECT 1 FROM CTE
WHERE NAME = A.NAME
AND 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