sql语句实现公交换乘，进来帮忙解决一下呢！

码拜

9年 ago

用的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;

解决方案

用SP来解决，参考SQLSERVER的
http://bbs.csdn.net/topics/330267483

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>