地铁换乘一次进一步修改。

 

alter proc proc_subone
(
  @start varchar(50),
  @end varchar(50),
  @cityid int
)
as
select tb.ss_station_name,tbscid,tescid,tb.sl_id as startid,td.sl_id  as endid,tb.sl_line_name as startname,td.sl_line_name as endname from (
select ss_station_name,sc_id as tbscid,tb.sl_id,sl_line_name from(
select * from subway_station where sl_id in (select sl_id from (select * from subway_station where ss_station_name=@start) as ta))as tb
inner join subway_line as tc on tb.sl_id = tc.sl_id
) as tb
inner join
(
select ss_station_name,sc_id as tescid,te.sl_id,sl_line_name from(
select * from subway_station where sl_id in (select sl_id from (select * from subway_station where ss_station_name=@end) as tc))as te
inner join subway_line as tf on te.sl_id = tf.sl_id
) as td
on tb.ss_station_name = td.ss_station_name and tbscid=@cityid and tescid=@cityid group by td.sl_line_name,tb.sl_line_name,tb.sl_id,td.sl_id,tb.ss_station_name,tbscid,tescid

 

区别于上次，加城市限制