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

whysshndyy 2015-05-12 02:26:11
用的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;
...全文
402 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_42498462 2018-12-27
  • 打赏
  • 举报
 回复
怎么把这个数据库查询的结果在网页上显示出来啊
scsc112_sc 2016-03-06
  • 打赏
  • 举报
 回复
亲。你好，你最终一次换乘。两次换乘怎么实现的？sql语句？
whysshndyy 2015-05-12
  • 打赏
  • 举报
 回复
谢谢大神们的帮助，终于把结果显示在网页上显示正常了。。。
whysshndyy 2015-05-12
  • 打赏
  • 举报
 回复
引用 5 楼 ACMAIN_CHM 的回复:
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>
谢谢版主帮我想这个问题，非常感谢你！！！
ACMAIN_CHM 2015-05-12
  • 打赏
  • 举报
 回复 
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>
wwwwb 2015-05-12
  • 打赏
  • 举报
 回复
不能用SQL来解决换乘问题
whysshndyy 2015-05-12
  • 打赏
  • 举报
 回复
引用 2 楼 wwwwb 的回复:
用SP来解决，参考SQLSERVER的 http://bbs.csdn.net/topics/330267483
没学过存储过程，没怎么看懂，不能用我写的直达查询那样实现吗？
wwwwb 2015-05-12
  • 打赏
  • 举报
 回复
用SP来解决，参考SQLSERVER的 http://bbs.csdn.net/topics/330267483
whysshndyy 2015-05-12
  • 打赏
  • 举报
 回复
大神些，赶紧进来帮小弟解决个问题呢，由于不会写存储过程，能不能用sql语句实现呢，数据不是很大，不用考虑最短路径什么的
经典SQL语句大全（文档）
经典SQL语句大全（文档）,绝对实用，都是一些常用的语句，可以帮助你很快找到灵感！
数据库SQL基本语句（半天即可学会，轻松简单）
只要你花半天时间，就能轻松掌握SQL基本语句的使用方式，很好的参考资料，主要看文章中的例子，因为例子具有可读连续性，因此，文档描述非常简单，容易掌握，也较通俗易懂！与大家分享！与大家共享！
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程...真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
sql语句 实现分页
      sql语句 实现分页 /* 分页思想：比如你要每页获取10条记录，当你显示第5页的记录时， 也就是选取第40条至50条的记录.首先应该从所有的记录集中选取 50条记录，同时进行倒序,再从中选10条，就完成工作了。 下面是一个具体的例子，从Northwind的Orders表中选取OrderID大于@OrderID的记录集， 分10条每页显示. */ --有查询条件的分页存储...
MySQL

56,687

社区成员

56,710

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章