注册 登录
编程论坛 SQL Server论坛

关于公交查询的算法~~希望大家有时间帮忙指点一二啊!!!

v123981499 发布于 2008-06-04 21:21, 946 次点击
这是关于中转站换乘的一个存储过程,在执行查询的时候,直达路线可显示,但换乘就没有显示了(即else后的语句),貌似也没有什么语法错误。第一个if是起点和终点在一条线路上的,else是关于中转站的查找及换车的路线。其中,lineid为线路的名称,stationname为站点的名称,希望大家可以帮帮我,找出其中的错误和不足之处,小妹在此感激不尽!!! ( 可QQ联系:123981499,请注明来意,谢谢!)

CREATE procedure s_to_s_select
(  
 @BeginStation varchar(20),
 @EndStation   varchar(20)
)
as
if exists     
 (     
        select *                   --起点和终点在同一条线路上
        from 线路表 as a
        where StationName=@BeginStation   
        and      
        StationName in                        
                            (
                                select StationName           
                                 from 线路表  as b
                                  where StationName=@EndStation   
                                   and  a.LineID=b.LineID
       )
  )      
                           
     select *                   --起点和终点在同一条线路上
        from 线路表 as a
        where StationName=@BeginStation
        and      
        StationName in                        
                            (
                                select StationName           
                                 from 线路表  as b
                                  where StationName=@EndStation   
                                   and  a.LineID=b.LineID
       )   order by Turn
   
else   
      if exists               --经过两站的不同线路中有交点
     (  select *                       
        from 线路表 as a
        where stationname in (                --经过起点的线路上的所有点
                                           select stationname
                                           from 线路表 as b
                                           where lineid in (
                                                                    select lineid
                                                                     from 线路表 as c
                                                                      where stationname like rtrim (@BeginStation)
                                                                  )
                                           )
       and      
                  stationname in (                        --过终点
                                           select stationname
                                           from 线路表 as b
                                           where lineid in (
                                                                    select lineid
                                                                     from 线路表 as c
                                                                      where stationname like rtrim (@EndStation)
                                                                  )
                                           )
                  )
        select *                       --查找线路
        from 线路表 as a
        where stationname in (
                                           select stationname
                                           from 线路表  as b
                                           where lineid in (
                                                                    select lineid
                                                                     from 线路表 as c
                                                                      where stationname like rtrim (@BeginStation)
                                                                  )
                                           )
       and     
                  stationname in (
                                           select stationname
                                           from 线路表 as b
                                           where lineid in (
                                                                    select lineid
                                                                     from 线路表 as c
                                                                      where stationname  like rtrim (@EndStation)
                                                                  )  
                                           )
        and     lineid in (
                            select lineid
                            from 线路表  as b
                             where stationname like rtrim (@BeginStation)                          
                           )
        and     lineid in (
                            select lineid
                            from 线路表  as b
                             where stationname like rtrim (@EndStation)           
                           )
       order by turn
GO[/b]
0 回复
1