oracle的sql语句问题，请熟悉sql语句的人过来帮忙

Question

  公交线路主表 way  线路id(wayid)   线路名称(wayname)   备注
                       1               100       
                    2               200  
                    3               300
                    4               500
  线路明细表waydetail    公交站id(waydetail )  站名(waydetailname)  线路id(wayid)  顺序(SEQ)  备注
                              20                A01                      1             1
                          21                A02                      1             2
                          22                A03                      1             3
                          23                A04                      1             4
                          24                B01                      2             1
                          25                B02                      2             2
                          26                B03                      2             3
                          27                B04                      2             4
                          28                B05                      2             5 某人的乘车记录penson      id    personid     线路id(wayid)          站名     
                          1        1           1                   A01
                          2        1           1                   A03
                          3        1           2                   B04
                          4        1           2                   B02通过sql求得       某人完整经过的站点                   上面 结果       personid     站名 
                                          
                                       1         A01    
                                       1         A02
                                  1         A03    
                                  1         B04    
                                  1         B03
                                  1         B02    
                          
                          
                              
                  

Answer 1

公交线路主表 way  线路id(wayid)  线路名称(wayname)  备注 
                    1              100
                    2              200  
                    3              300 
                    4              500 
  线路明细表waydetail    公交站id(waydetail )  站名(waydetailname)  线路id(wayid)  顺序(SEQ)  备注 
                          20                A01                      1            1
                          21                A02                      1            2 
                          22                A03                      1            3 
                          23                A04                      1            4 
                          24                B01                      2            1 
                          25                B02                      2            2 
                          26                B03                      2            3 
                          27                B04                      2            4 
                          28                B05                      2            5 某人的乘车记录penson      id    personid    线路id(wayid)          站名    
                          1        1          1                  A01
                          2        1          1                  A03 
                          3        1          2                  B04 
                          4        1          2                  B02 通过sql求得      某人完整经过的站点                   上面 结果          personid    站名    
                                       1        A01    
                                  1        A02 
                                  1        A03    
                                  1        B04    
                                  1        B03 
                                  1        B02    

Answer 2

主要是顺序的问题  我已经能得出 它经过的车站  在排序有问题 
                                   想要的结果是
                                    personid    站名    
                                       1        A01    
                                  1        A02 
                                  1        A03    
                                  1        B04    
                                  1        B03 
                                  1        B02                                 但有时出来                                  personid    站名    
                                       1        A01    
                                  1        A02 
                                  1        A03    
                                  1        B02   
                                  1        B03 
                                  1        B04
   就是B线路的顺序还是正向，正确的走向是B04站到B02，应该反向
    

Answer 3

--给个相关的例,不过是SQL SERVER的./*
标题：一个有关车站换乘的例
作者：爱新觉罗.毓华 
时间：2008-05-22
地点：广东深圳
*//*
数据库中有一张表记录了车号和它经过的站点,  
比如:  
车号    所经过的站点  
1       a,b,c,d,e,f  
2       k,n,m,d,l,q,w  
3       aa,ee,ww,oo,ef,cc,qw  
4       aa,o,p,d,l,q,w,s  
如果我现在要查从a到w可以乘做的车次,可以看出要转车,也就是坐1路到d站点,
然后在坐2路或是4路  
现在我要写一个存储过程输入两个站点的名称比如a,w要得到  车次1   车次2   转车点  
1        2,4    d  
也就是先坐1路,再在d站点转2路或4路  请问这个存储过程怎么写
*/create table tb(车号 int,所经过的站点 varchar(50))
insert into tb values(1 ,      'a,b,c,d,e,f')  
insert into tb values(2 ,      'k,n,m,d,l,q,w')  
insert into tb values(3 ,      'aa,ee,ww,oo,ef,cc,qw')  
insert into tb values(4 ,      'aa,o,p,d,l,q,w,s')
go
--生成一临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b declare @s1 as varchar(10)
declare @s2 as varchar(10)
set @s1 = 'a'
set @s2 = 'w'select * from 
(
  SELECT A.车号 , 站点 = SUBSTRING(A.[所经过的站点], B.id, CHARINDEX(',', A.[所经过的站点] + ',', B.id) - B.id)
  FROM tb A, # B
  WHERE SUBSTRING(',' + A.[所经过的站点], B.id, 1) = ',' 
) t 
where (站点 = @s2 or 站点 in
(
  SELECT 站点 = SUBSTRING(A.[所经过的站点], B.id, CHARINDEX(',', A.[所经过的站点] + ',', B.id) - B.id)
  FROM tb A, # B
  WHERE SUBSTRING(',' + A.[所经过的站点], B.id, 1) = ',' 
  and 车号 in (select 车号 from tb where charindex(',' + @s1 + ',' , ',' + 所经过的站点 + ',') > 0)
) ) and 车号 not in (select 车号 from tb where charindex(',' + @s1 + ',' , ',' + 所经过的站点 + ',') > 0)
/*
车号          站点                                                 
----------- -------------------------------------------------- 
2           d
2           w
4           d
4           w（所影响的行数为 4 行）
*/-------------------------------------------------------------------------------------------------------------
select 车号 from 
(
  SELECT A.车号 , 站点 = SUBSTRING(A.[所经过的站点], B.id, CHARINDEX(',', A.[所经过的站点] + ',', B.id) - B.id)
  FROM tb A, # B
  WHERE SUBSTRING(',' + A.[所经过的站点], B.id, 1) = ',' 
) t 
where (站点 = @s2 or 站点 in
(
  SELECT 站点 = SUBSTRING(A.[所经过的站点], B.id, CHARINDEX(',', A.[所经过的站点] + ',', B.id) - B.id)
  FROM tb A, # B
  WHERE SUBSTRING(',' + A.[所经过的站点], B.id, 1) = ',' 
  and 车号 in (select 车号 from tb where charindex(',' + @s1 + ',' , ',' + 所经过的站点 + ',') > 0)
) ) and 车号 not in (select 车号 from tb where charindex(',' + @s1 + ',' , ',' + 所经过的站点 + ',') > 0)
group by 车号 
having count(*) = 2
/*
车号          
----------- 
2
4（所影响的行数为 2 行）
*/drop table tb , #

Answer 4

第一次查询时将penson表的id一起查出并按此id排序即可。