/**********************************
测试题:已知如下一张表,它是某人的行程路线,请找出他的行程。
要求:用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' )
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
;with t as
(
select * from temps where name='张三' and leave='A'
union all
select b.* from t,temps b where t.name=b.name and t.arrive=b.leave
)
select top 1 name,stuff((select ','+leave from t for xml path('')),1,1,'') as leave from t
/*
name leave
---------- ------------
张三 A,B,C,D,E,F(1 行受影响)
*/
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
)
(
select *,path=cast(leave+'→'+arrive as varchar(1000)),level=1 from temps-- where name='张三' and leave='A'
union all
select b.*,path=cast(t.path+'→'+b.arrive as varchar(1000)),level=level+1 from t,temps b where t.name=b.name and t.arrive=b.leave
)
select name,path from t as a where level=(select max(level) from t as b where a.name=b.name)
/*
name leave
---------- ------------
李四 A→B→C→D→E→F
张三 A→B→C→D→E→F→G(2 行受影响)
*/改进版
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