公交车查询的数据库设计
发布网友
共5个回答
热心网友
create table station
(busno varchar2(20),--车号
st1 varchar2(20),--站1
st2 varchar2(20),--站2
sttime number,--相距时间
stdis number,--相距距离
ststart varchar2(20),--该车号起始站
stend varchar2(20), --该车号终点站
stcost number, --该车号花费钱
)
测试数据
insert into station values ('k1','a1','b1',10,10,'a1','a10',1.2);
insert into station values ('k1','b1','a2',12,12,'a1','a10',1.2);
insert into station values ('k1','a2','b2',8,10,'a1','a10',1.2);
insert into station values ('k1','b2','a3',11,11,'a1','a10',1.2);
insert into station values ('k1','a3','b3',5,15,'a1','a10',1.2);
insert into station values ('k1','b3','a10',3,16,'a1','a10',1.2);
insert into station values ('k2','b1','b2',2,18,'b1','a5',1.3);
insert into station values ('k2','b2','a3',10,12,'b1','a5',1.3);
insert into station values ('k2','a3','b4',5,10,'b1','a5',1.3);
insert into station values ('k2','b4','a4',10,10,'b1','a5',1.3);
insert into station values ('k2','a4','c1',5,10,'b1','a5',1.3);
insert into station values ('k2','c1','a5',10,10,'b1','a5',1.3);
insert into station values ('k3','a1','c1',10,10,'a1','c2',1.1);
insert into station values ('k3','c1','b2',10,10,'a1','c2',1.1);
insert into station values ('k3','b2','a3',10,10,'a1','c2',1.1);
insert into station values ('k3','a3','c2',10,11,'a1','c2',1.1);
insert into station values ('k4','c1','b1',10,10,'c1','c3',1.4);
insert into station values ('k4','b1','c2',20,10,'c1','c3',1.4);
insert into station values ('k4','c2','b2',10,11,'c1','c3',1.4);
insert into station values ('k4','b2','a1',10,10,'c1','c3',1.4);
insert into station values ('k4','a1','c3',10,10,'c1','c3',1.4);
insert into station values ('k5','a1','b1',7,10,'a1','a10',1.2);
insert into station values ('k5','b1','a2',10,12,'a1','a10',1.2);
insert into station values ('k5','a2','b2',10,10,'a1','a10',1.2);
insert into station values ('k5','b2','a3',10,13,'a1','a10',1.2);
insert into station values ('k5','a3','b3',6,10,'a1','a10',1.2);
insert into station values ('k5','b3','c1',10,14,'a1','a10',1.2);
insert into station values ('k5','c1','c2',10,10,'a1','a10',1.2);
insert into station values ('k5','c2','c3',10,15,'a1','a10',1.2);
insert into station values ('k5','c3','a10',10,10,'a1','a10',1.2);
insert into station values ('k6','a1','b1',9,10,'a1','c4',1.5);
insert into station values ('k6','b1','c1',10,10,'a1','c4',1.5);
insert into station values ('k6','c1','d1',10,10,'a1','c4',1.5);
insert into station values ('k6','d1','a2',8,10,'a1','c4',1.5);
insert into station values ('k6','a2','b3',10,10,'a1','c4',1.5);
insert into station values ('k6','b3','c3',3,5,'a1','c4',1.5);
insert into station values ('k6','c3','d2',2,6,'a1','c4',1.5);
insert into station values ('k6','d2','c4',20,10,'a1','c4',1.5);
查询车次K1的路线
select st1,st2,busno,sys_connect_by_path(st1,'->')||'->'||st2 路线
from (
select * from station
where st2<>'a10' and busno = 'k1')
start with st1 = 'a1'
connect by nocycle prior st2 = st1
/
SQL> col 路线 format a30
SQL> /
ST1 ST2 BUSN 路线
---- ---- ---- ------------------------------
a1 b1 k1 ->a1->b1
b1 a2 k1 ->a1->b1->a2
a2 b2 k1 ->a1->b1->a2->b2
b2 a3 k1 ->a1->b1->a2->b2->a3
a3 b3 k1 ->a1->b1->a2->b2->a3->b3
起点站a1，到站c3，转车4次以内的转站顺序和乘车顺序
1 SELECT distinct st2,sys_connect_by_path(st1,'->')||'->'||st2 转站,
2 sys_connect_by_path(busno,'->')||'->'||busno 乘车,level
3 from (
4 select * from station
5 where st2<>'a1') aa
6 where level < 5 and st2 = 'c3'
7 start with st1 = 'a1'
8* connect by nocycle prior st2 = st1
SQL> /
ST2 转站 乘车 LEVEL
---------- -------------------- -------------------- -----
c3 ->a1->b1->c2->c3 ||->k1->k4->k5->k5 3
c3 ->a1->b1->a2->b3->c3|| ->k5->k5->k6->k6->k6 4
c3 ->a1->b1->c1->c2->c3|| ->k6->k6->k5->k5->k5 4
c3 ->a1->b1->a2->b3->c3 ||->k1->k1->k6->k6->k6 4
c3 ->a1->b1->c1->c2->c3 ||->k5->k6->k5->k5->k5 4
c3 ->a1->c1->b1->c2->c3 ||->k3->k4->k4->k5->k5 4
c3 ->a1->c3 ||->k4->k4 1
c3 ->a1->b1->a2->b3->c3|| ->k6->k1->k6->k6->k6 4
c3 ->a1->b1->a2->b3->c3 ||->k1->k5->k6->k6->k6 4
c3 ->a1->c1->c2->c3 ||->k3->k5->k5->k5 3
c3 ->a1->b1->c2->c3|| ->k5->k4->k5->k5 3
ST2 转站 乘车 LEVEL
---------- -------------------- -------------------- -----
c3 ->a1->b1->c1->c2->c3 ||->k1->k6->k5->k5->k5 4
c3 ->a1->b1->a2->b3->c3|| ->k5->k1->k6->k6->k6 4
c3 ->a1->b1->a2->b3->c3|| ->k6->k5->k6->k6->k6 4
c3 ->a1->b1->c2->c3 ||->k6->k4->k5->k5 3
已选择15行。
再来一例
起点站a2，到站a3，转车4次以内的转站顺序和乘车顺序，按转车次数升序
1 SELECT distinct st2,sys_connect_by_path(st1,'->')||'->'||st2 转站,
2 sys_connect_by_path(busno,'->')||'->'||busno 乘车,level
3 from (
4 select * from station
5 where st2<>'a2') aa
6 where level < 5 and st2 = 'a3'
7 start with st1 = 'a2'
8 connect by nocycle prior st2 = st1
9* order by level
SQL> /
ST2 转站 乘车 LEVEL
---------- -------------------- -------------------- -----
a3 ->a2->b2->a3|| ->k1->k1->k1 2
a3 ->a2->b2->a3|| ->k1->k2->k2 2
a3 ->a2->b2->a3|| ->k1->k3->k3 2
a3 ->a2->b2->a3|| ->k1->k5->k5 2
a3 ->a2->b2->a3|| ->k5->k1->k1 2
a3 ->a2->b2->a3|| ->k5->k2->k2 2
a3 ->a2->b2->a3|| ->k5->k3->k3 2
a3 ->a2->b2->a3|| ->k5->k5->k5 2
a3 ->a2->b3->c1->b2->a3|| ->k6->k5->k3->k1->k1 4
a3 ->a2->b3->c1->b2->a3|| ->k6->k5->k3->k2->k2 4
a3 ->a2->b3->c1->b2->a3|| ->k6->k5->k3->k3->k3 4
ST2 转站 乘车 LEVEL
---------- -------------------- -------------------- -----
a3 ->a2->b3->c1->b2->a3|| ->k6->k5->k3->k5->k5 4
已选择12行。
设计表的时候加入了站站之间的时间和站站之间的距离，当然还可以加个字段每个车次花费的钱，这样的话再加条件可以取最短时间，最短距离，最短花费钱，时间不够了。。先去办离职手续。。。
有看官闲的话，可以把最短时间，最短距离，最短花费钱的查询设计出来哈！
热心网友
我觉得这是一个路由的问题，而不是简单的关系表，从算法上来说比较类似于路由器编程，现在比较简单的做法就是用二叉树穷举来实现，复杂一点的做法可以用矢量来实现。
基本表设计：
create table 站名
（
站ID
站名
经过车ID
）
create table 车
（
车ID
车名
）
create table 站关系
（
站ID
下一站ID
经过车ID
）
create table 车关系
（
车ID
始发站ID
终点站ID
）
建好表以后，在程序中列举所当前站所经车，并穷举经过车下一站ID，递归本段代码，穷举下一站所经车，再穷举下一站经过车下一站ID，直到终点站或不可达，最终得到车站列表和车列表，取最短数。
热心网友
穷举法.
如果你想实现任一两站点转 N 趟车都能有公交线路可达的话(前提是两站确实存在此可达路径),只有此法!
匹配法
如果你只想实现转一趟车,那么用匹配法就可以了.那就是:
(A:)先把所有经过发站的所有线路保存,
(B:)然后把所有经过终点站的线路保存,再进行匹配.
如果A线路与B线路*同拥有同一站点,那么找到一条可达线路.
热心网友
你是想通过设计一个关系数据库表结构来保存所有可能的乘车情况，而程序本身只用简单的select语句就实现你说的乘车查询功能吧？
我觉得这个不可能，也许是我做不到吧。你想走捷径还不如想想怎么用nb的sql查询语句来实现。
要不去csdn上面问吧，那上面才是牛人辈出。
热心网友
呵呵，为了不抢那些工人的饭碗，知道我也不说
