Two columns of data, respectively the train departure city and reached the city, each line has a direct route, now have to start from lanzhou to Beijing, need to find a transit, such as lanzhou - xi 'an - Beijing, this how to solve? Thank you for your advice on
The field name a, b
Lanzhou Beijing
Xi 'an, chongqing

xi 'an BeijingLanzhou xian

CodePudding user response:

 
If object_id (' tempdb for.. # # TAB ') is not null drop table TAB 
The create table # TAB (s nvarchar (Max), e nvarchar (Max)) 

Insert into # TAB (s, e) 
Select 'lanzhou', 'Beijing' union all select 'xi 'an', 'chongqing' union all 
Select 'xi 'an', 'Beijing' union all select 'lanzhou', 'xi 'an' union all 
Select 'lanzhou', 'zhengzhou' union all select 'xi 'an', 'zhengzhou' union all 
Select 'zhengzhou', 'Beijing' union all select 'Beijing', 'tianjin' 

Select * from # TAB 

; As with t (
Select s, e, 0 as lv, s + + e as' - '(path) from # TAB where s=' lanzhou '
Union all 
Select t.s, # TAB. J e, t.l v + 1 as lv, t.p ath + + # TAB. '-' the from e # TAB inner join t on t.e=# TAB. S 
) 
Select * from t order by lv 

The result set s: departure point; E: the terminal; Lv: transfer times. What do you want the results add to filter the where condition,