用的Mysql 5.5数据库
–站点表
create table station_info(
station_id int(4) primary key auto_increment,
station_name varchar(20) not null unique
);
–路线表
create table route_info(
route_id int(4),
route_name varchar(20) not null unique,
start_stationId int not null, –起始站点
end_stationId int not null, –终点站点
);
–路线站点表
create table route_station(
id int(4) primary key auto_increment,
route_id int(4),
station_id int(4) not null,
position int(2) not null,–站点所处路线中位置
upordown int(1) not null –上行或下行(1上2下)
);
本人写了一个直达查询语句：
select distinct rs.route_id from route_station rs, route_station rsa
where rs.route_id = rsa.route_id and (rs.station_id = 1 and rsa.station_id = 5)
对于存储过程不会写，能否用sql语句实现，在处理一次换乘和二次换乘的时候写了很多语句，结果就乱了，不用考虑什么最短路径之类的
例如在处理一次换乘的时候，本人是先找出换乘站点，然后再通过换乘站点通过直达查询找出换乘路线，多个换乘站点的时候
不知道怎么处理
二次换乘先是找出换乘路线，然后再后面处理感觉比较混乱了
例如说本人输入S1-S8,一次换乘想要输出的效果如下：
route_id station_id（换乘站点） route_id(换乘路线)
10 S2/S7 11
10 S2 13
14 S9 12
14 S6 11
测试语句：
10路：S1 – S2 – S3 – S4 – S5 – S7
11路：S2 – S6 – S7 – S8
12路：S8 – S9 – S10
13路：S11 – S2 – S8
14路：S1 – S12 – S6 – S9
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″);
insert into station_info values(8,”S8″);
insert into station_info values(9,”S9″);
insert into station_info values(10,”S10″);
insert into station_info values(11,”S11″);
insert into station_info values(12,”S12″);
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″,8,10);
insert into route_info values(13,”13″,11,8);
insert into route_info values(14,”14″,1,9);
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,7,6,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,8,1,1);
insert into route_station values(12,12,9,2,1);
insert into route_station values(13,12,10,3,1);
insert into route_station values(14,13,11,1,1);
insert into route_station values(15,13,2,2,1);
insert into route_station values(16,13,8,3,1);
insert into route_station values(17,14,1,1,1);
insert into route_station values(18,14,12,2,1);
insert into route_station values(19,14,6,3,1);
insert into route_station values(20,14,9,4,1);
commit;
–站点表
create table station_info(
station_id int(4) primary key auto_increment,
station_name varchar(20) not null unique
);
–路线表
create table route_info(
route_id int(4),
route_name varchar(20) not null unique,
start_stationId int not null, –起始站点
end_stationId int not null, –终点站点
);
–路线站点表
create table route_station(
id int(4) primary key auto_increment,
route_id int(4),
station_id int(4) not null,
position int(2) not null,–站点所处路线中位置
upordown int(1) not null –上行或下行(1上2下)
);
本人写了一个直达查询语句：
select distinct rs.route_id from route_station rs, route_station rsa
where rs.route_id = rsa.route_id and (rs.station_id = 1 and rsa.station_id = 5)
对于存储过程不会写，能否用sql语句实现，在处理一次换乘和二次换乘的时候写了很多语句，结果就乱了，不用考虑什么最短路径之类的
例如在处理一次换乘的时候，本人是先找出换乘站点，然后再通过换乘站点通过直达查询找出换乘路线，多个换乘站点的时候
不知道怎么处理
二次换乘先是找出换乘路线，然后再后面处理感觉比较混乱了
例如说本人输入S1-S8,一次换乘想要输出的效果如下：
route_id station_id（换乘站点） route_id(换乘路线)
10 S2/S7 11
10 S2 13
14 S9 12
14 S6 11
测试语句：
10路：S1 – S2 – S3 – S4 – S5 – S7
11路：S2 – S6 – S7 – S8
12路：S8 – S9 – S10
13路：S11 – S2 – S8
14路：S1 – S12 – S6 – S9
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″);
insert into station_info values(8,”S8″);
insert into station_info values(9,”S9″);
insert into station_info values(10,”S10″);
insert into station_info values(11,”S11″);
insert into station_info values(12,”S12″);
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″,8,10);
insert into route_info values(13,”13″,11,8);
insert into route_info values(14,”14″,1,9);
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,7,6,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,8,1,1);
insert into route_station values(12,12,9,2,1);
insert into route_station values(13,12,10,3,1);
insert into route_station values(14,13,11,1,1);
insert into route_station values(15,13,2,2,1);
insert into route_station values(16,13,8,3,1);
insert into route_station values(17,14,1,1,1);
insert into route_station values(18,14,12,2,1);
insert into route_station values(19,14,6,3,1);
insert into route_station values(20,14,9,4,1);
commit;
解决方案
10
用SP来解决，参考SQLSERVER的
http://bbs.csdn.net/topics/330267483
http://bbs.csdn.net/topics/330267483
90
mysql> select * from station_info; +--+--+ | station_id | station_name | +--+--+ | 1 | S1 | | 10 | S10 | | 11 | S11 | | 12 | S12 | | 2 | S2 | | 3 | S3 | | 4 | S4 | | 5 | S5 | | 6 | S6 | | 7 | S7 | | 8 | S8 | | 9 | S9 | +--+--+ 12 rows in set (0.00 sec) mysql> select * from route_info; +--+--+--+--+ | route_id | route_name | start_stationId | end_stationId | +--+--+--+--+ | 10 | 10 | 1 | 7 | | 11 | 11 | 2 | 8 | | 12 | 12 | 8 | 10 | | 13 | 13 | 11 | 8 | | 14 | 14 | 1 | 9 | +--+--+--+--+ 5 rows in set (0.00 sec) mysql> select * from route_station; +--+--+--+--+--+ | id | route_id | station_id | position | upordown | +--+--+--+--+--+ | 1 | 10 | 1 | 1 | 1 | | 2 | 10 | 2 | 2 | 1 | | 3 | 10 | 3 | 3 | 1 | | 4 | 10 | 4 | 4 | 1 | | 5 | 10 | 5 | 5 | 1 | | 6 | 10 | 7 | 6 | 1 | | 7 | 11 | 2 | 1 | 1 | | 8 | 11 | 6 | 2 | 1 | | 9 | 11 | 7 | 3 | 1 | | 10 | 11 | 8 | 4 | 1 | | 11 | 12 | 8 | 1 | 1 | | 12 | 12 | 9 | 2 | 1 | | 13 | 12 | 10 | 3 | 1 | | 14 | 13 | 11 | 1 | 1 | | 15 | 13 | 2 | 2 | 1 | | 16 | 13 | 8 | 3 | 1 | | 17 | 14 | 1 | 1 | 1 | | 18 | 14 | 12 | 2 | 1 | | 19 | 14 | 6 | 3 | 1 | | 20 | 14 | 9 | 4 | 1 | +--+--+--+--+--+ 20 rows in set (0.00 sec) mysql> select r1.route_name, -> group_concat(s.station_name SEPARATOR "/") as station_id, -> r2.route_name -> from route_station rs1, -> route_station rs2, -> route_station a, -> route_station b, -> route_info r1, -> route_info r2, -> station_info s -> where rs1.station_id=(select station_id from station_info where station_name="S1" ) -> and rs2.station_id=(select station_id from station_info where station_name="S8" ) -> and a.station_id=b.station_id -> and a.route_id=rs1.route_id -> and b.route_id=rs2.route_id -> and rs1.route_id=r1.route_id -> and rs2.route_id=r2.route_id -> and a.station_id=s.station_id -> group by r1.route_name, -> r2.route_name; +--+--+--+ | route_name | station_id | route_name | +--+--+--+ | 10 | S7/S2 | 11 | | 10 | S2 | 13 | | 14 | S6 | 11 | | 14 | S9 | 12 | +--+--+--+ 4 rows in set (0.00 sec) mysql>
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明sql语句实现公交换乘，进来帮忙解决一下呢！！
