公交查询系统SQL查询储存过程

线路查询:

CREATE PROCEDURE Link_Select
(
@Line varchar(10)
)
AS
SELECT *
FROM [线路属性]
WHERE bus LIKE Rtrim(@Line)
GO

站点查询:

CREATE PROCEDURE Site_Select
(
@Site varchar(20)
)
AS
SELECT *
FROM [线路表]
WHERE sname LIKE Rtrim(@Site)
GO

换乘查询:

算法描诉

假设需要从站点A到达站点C

1.搜索所有可以直达A的站点,站点名的集合为B1,通过A及{x| x in B1}中任一站点的车次的集合为L1

2.搜索所有可以直达C的站点,站点名的集合为B2,通过C及{x| x in B2}中任一站点的车次的集合为L2

3.求出B1与B2的交集为B,B中的站点即是站点A到达站点C的中转站

4.(foreach Site in B)   //通过遍历B中的站点，来得出“总经过站点数”最少的最优路线

   {

      从L1中找出A到达Site的最短线路,此线路为（起点站）

       从L2中找出C到达Site的最短线路,此线路为（终点站）

       A到达Site经过站点数 + C到达Site经过的站点数 = 总经过站点数          

   }

CREATE PROCEDURE StoS_Select
(
@Begin_Site nvarchar (20),
@End_Site nvarchar (20)
)
AS
SELECT DISTINCT TOP 10 m.起点线路, m.起点站, m.中转站, n.终点站, n.终点线路, ABS(M.经过站点数 + N.经过站点数) AS 总经过站点数
FROM    (SELECT A.bus AS 起点线路 , A.sname AS 起点站 , B.sname AS 中转站 ,ABS(A.snum - B.snum ) AS 经过站点数
FROM 线路表 AS A, 线路表 AS B
WHERE A.sname LIKE Rtrim(@Begin_Site) AND (A.sname <> B.sname AND A.bus = B.bus))
AS m(起点线路,起点站,中转站,经过站点数)
,
(SELECT C.sname AS 中转站 ,D.sname AS 终点站 ,D.bus AS 终点线路 ,ABS(C.snum - D.snum ) AS 经过站点数
FROM 线路表 AS C, 线路表 AS D
WHERE D.sname LIKE Rtrim(@End_Site) AND (C.sname <> D.sname AND C.bus = D.bus))
AS n(中转站,终点站,终点线路,经过站点数)
WHERE m.中转站 = n.中转站 AND (m.起点线路 <> n.终点线路) ORDER BY 总经过站点数
GO