--试着写一下,请各位不要拍砖:
1 建立一张临时表#(Line,BusID,BusStation,BusStationIndex,Level,gid);
2 先查询有无直达的车,若无影响行数,则说明有直达的车次;
3 若有影响行数(转车信息不能大于4000,数据类型限制),说明需要转车;
4 下面是一个临时表和原公交信息表的联接查询,完成经过几站,转几次车用拼接的转车信息;
--最后的不能讲得细,等LX解惑;
--以前做过列车查询,是用集合嵌套做的,也挺绕,不过逻辑还可以;

CREATE   PROC   p_qry  
      @Station_Start   nvarchar(10),  
      @Station_Stop     nvarchar(10)  
 AS  
  SET   NOCOUNT   ON  
  DECLARE   @l   int  
  SET   @l=0  
  SELECT   BusID,BusStation,  
           Line=CAST('('+RTRIM(BusID)+':'+RTRIM(BusStation) as nvarchar(4000)),  
         BusStationIndex=BusStationIndex,  
         Level=@l,  
         gid=1  
  INTO   #   FROM  table_Bus              
  WHERE   BusStation=@Station_Start  
  WHILE   @@ROWCOUNT>0                   
  AND NOT EXISTS(SELECT * FROM # WHERE BusStation=@Station_Stop OR LEN(Line)=4000)  
  BEGIN  
      SET   @l=@l+1  
      INSERT   #(Line,BusID,BusStation,BusStationIndex,Level,gid)  
      SELECT    
          Line=a.Line+
        CASE  
            WHEN  a.BusID=b.BusID THEN  N'->'+RTRIM(b.BusStation)  
            ELSE   N') => ('+RTRIM(b.BusID) +N':'+RTRIM(b.BusStation) 
        END,  
          b.BusID,b.BusStation,b.BusStationIndex,@l,  
          CASE    
              WHEN   a.BusID=b.BusID   THEN   a.gid    ---?when是什么语法哦帮助也没找到
              ELSE   a.gid+1   
        END  
      FROM   #   a,table_Bus   b  
      WHERE   a.Level=@l-1   AND(a.BusID=b.BusID 
        AND(a.BusStationIndex=b.BusStationIndex+1 OR a.BusStationIndex=b.BusStationIndex-1)  
          OR    
          a.BusStation=b.BusStation   AND   a.BusID<>b.BusID)  
  END  
  SELECT   N'起点站'=@Station_Start  
      ,N'终点站'=@Station_Stop  
      ,N'转车次数'=gid-1  
      ,N'经过站数'=CASE   WHEN   gid<3   THEN   @l   ELSE   @l-gid+2   END  
      ,N'乘车线路'=Line+N')'    
  FROM   #    
  WHERE   Level=@l    
      AND   BusStation=@Station_Stop  
      IF   @@ROWCOUNT   =0  
        begin  
              SELECT   *   FROM   #  
        end
  GO 

Case When Then else End

 CREATE   PROC   p_qry  
  @Station_Start   nvarchar(10),  
  @Station_Stop     nvarchar(10)  
  AS  
  SET   NOCOUNT   ON  
  DECLARE   @l   int  
  SET   @l=0 
  --把通过起始站的线路信息插入到临时表 Level 站序号 gid 换乘次数
  SELECT   BusID,BusStation,  
  Line=CAST('('+RTRIM(BusID)+':   '+RTRIM(BusStation)   as   nvarchar(4000)),  
  BusStationIndex=BusStationIndex,  
  Level=@l,  
  gid=1  
  INTO   #   FROM   table_Bus  
  WHERE   BusStation=@Station_Start  

  WHILE   @@ROWCOUNT>0    
  AND   NOT   EXISTS(SELECT   *   FROM   #   WHERE   BusStation=@Station_Stop   OR   LEN(Line)=4000)  --如果没有找到结束站点循环
  BEGIN  
  SET   @l=@l+1  

  INSERT   #(Line,BusID,BusStation,BusStationIndex,Level,gid)  
  SELECT    
  Line=a.Line+CASE  
  WHEN   a.BusID=b.BusID   THEN   N'->'+RTRIM(b.BusStation)  
  ELSE   N')   =>   ('+RTRIM(b.BusID)  
  +N':   '+RTRIM(b.BusStation)   END,  
  b.BusID,b.BusStation,b.BusStationIndex,@l,  --LINE 每次都根据最后一个添加更新
  CASE    
  WHEN   a.BusID=b.BusID   THEN   a.gid    ---?when是什么语法哦帮助也没找到
  ELSE   a.gid+1   END  
  FROM   #   a,table_Bus   b  
  WHERE   a.Level=@l-1   AND( 
--查询当前站的同一班次车的上一站或者下一战 
  a.BusID=b.BusID   AND(  
  a.BusStationIndex=b.BusStationIndex+1  
  OR  
  a.BusStationIndex=b.BusStationIndex-1)  
  OR 
--查询当前站的不同班次车的上一站或者下一战    
  a.BusStation=b.BusStation   AND   a.BusID<>b.BusID)  --插入的哦临时表里 

  END  
  SELECT   N'起点站'=@Station_Start  
  ,N'终点站'=@Station_Stop  
  ,N'转车次数'=gid-1  
  ,N'经过站数'=CASE   WHEN   gid<3   THEN   @l   ELSE   @l-gid+2   END  
  ,N'乘车线路'=Line+N')'    
  FROM   #    
  WHERE   Level=@l    
  AND   BusStation=@Station_Stop  --查询出临时表里最后信息
  IF   @@ROWCOUNT   =0    
  SELECT   *   FROM   #  
  GO