Question

sql语句实现公交一次换乘，因为不会存储过程是怎么操作的，能否用sql关联方式实现

10路：S1 - S2 - S3 - S4 - S5 -S7
11路：S2 - S6 - S7 - S8
12路：S9 - S3 - S8

站点表主要信息 station_info(station_id,station_name)
路线表主要信息 route_info(route_id,route_name)
路线站点表主要信息 route_station(id,station_id,route_id,position)

现在我想通过sql语句实现一次换乘,输入站点S1和S8，打印效果如下:
路线 换乘站点 换乘路线
10 S2/S7 11
10 S3 12

create table station_info(
station_id int(4) primary key auto_increment, --站点ID
station_name varchar(20) not null unique --站点名称
);

insert into station_info values(1,'S1');
insert into station_info values(2,'S2');
insert into station_info values(3,'S3');
insert into station_info values(4,'S4');
insert into station_info values(5,'S5');
insert into station_info values(6,'S6');
insert into station_info values(7,'S7');
commit;

create table route_info(
route_id int(4) primary key, --线路ID
route_name varchar(20) not null unique, --线路名称
start_stationId int not null, --起始站点ID
end_stationId int not null, --终点站点ID
);

insert into route_info values(10,'10',1,7);
insert into route_info values(11,'11',2,8);
insert into route_info values(12,'12',9,8);
commit;

create table route_station(
id int(4) primary key auto_increment, --id
route_id int(4) primary key, --路线ID
station_id int(4) not null, --站点ID
position int(2) not null, --站点顺序
upOrdown int(1) not null --上行或下行（1 上行， 2 下行）
);

insert into route_station values(1,10,1,1，1);
insert into route_station values(2,10,2,2，1);
insert into route_station values(3,10,3,3，1);
insert into route_station values(4,10,4,4，1);
insert into route_station values(5,10,5,5，1);
insert into route_station values(6,10,6,7，1);
insert into route_station values(7,11,2,1，1);
insert into route_station values(8,11,6,2，1);
insert into route_station values(9,11,7,3，1);
insert into route_station values(10,11,8,4，1);
insert into route_station values(11,12,9,1，1);
insert into route_station values(12,12,3,2，1);
insert into route_station values(12,12,8,3，1);
commit;

Answer 1

这个比较烦，应该还是可以做的：
（不换乘的结果）
select a.route_id, NULL, a.route_id
from route_station a
where 1 in (select station_id from a)
and 8 in (select station_id from a);

换乘一次：
select a.route_id,a.station_id, b.route_id
from route_station a, route_station b
where a.route_id<>b.route_id and a.station_id=b.station_id
and 1 in (select station_id from a) and 8 in (select station_id from b)

　换乘两次的按照换乘一次的扩展一下应该不难。