CREATE TABLE T_Line(
ID????? nvarchar(10),? --公交線路號
Station nvarchar(10),? --站點名稱
Orders? int)?????????? --行車方向(通過它反應每個站的上一個、下一個站)
INSERT T_Line
SELECT N'8路'? ,N'站A',1 UNION ALL
SELECT N'8路'? ,N'站B',2 UNION ALL
SELECT N'8路'? ,N'站C',3 UNION ALL
SELECT N'8路'? ,N'站D',4 UNION ALL
SELECT N'8路'? ,N'站J',5 UNION ALL
SELECT N'8路'? ,N'站L',6 UNION ALL
SELECT N'8路'? ,N'站M',7 UNION ALL
SELECT N'20路' ,N'站G',1 UNION ALL
SELECT N'20路' ,N'站H',2 UNION ALL
SELECT N'20路' ,N'站I',3 UNION ALL
SELECT N'20路' ,N'站J',4 UNION ALL
SELECT N'20路' ,N'站L',5 UNION ALL
SELECT N'20路' ,N'站M',6 UNION ALL
SELECT N'255路',N'站N',1 UNION ALL
SELECT N'255路',N'站O',2 UNION ALL
SELECT N'255路',N'站P',3 UNION ALL
SELECT N'255路',N'站Q',4 UNION ALL
SELECT N'255路',N'站J',5 UNION ALL
SELECT N'255路',N'站D',6 UNION ALL
SELECT N'255路',N'站E',7 UNION ALL
SELECT N'255路',N'站F',8
GO

--乘車線路查詢存儲過程
CREATE PROC p_qry
@Station_Start nvarchar(10),
@Station_Stop? nvarchar(10)
AS
SET NOCOUNT ON
DECLARE @l int
SET @l=0
SELECT ID,Station,
?Line=CAST('('+RTRIM(ID)+': '+RTRIM(Station) as nvarchar(4000)),
?Orders=Orders,
?[Level]=@l
INTO # FROM T_Line
WHERE Station=@Station_Start
WHILE @@ROWCOUNT>0
?AND NOT EXISTS(SELECT * FROM # WHERE Station=@Station_Stop )
BEGIN
?SET @l=@l+1
?INSERT #(Line,ID,Station,Orders,[Level])
?SELECT
??Line=a.Line+CASE
???WHEN a.ID=b.ID THEN N'->'+RTRIM(b.Station)
???ELSE N') ∝ ('+RTRIM(b.ID)
????+N': '+RTRIM(b.Station) END,
??b.ID,b.Station,b.Orders,@l
?FROM # a,T_Line b
?WHERE a.[Level]=@l-1
??AND(a.Station=b.Station AND a.ID<>b.ID
???OR a.ID=b.ID AND(
????a.Orders=b.Orders+1
????OR
????a.Orders=b.Orders-1))
??AND LEN(a.Line)<4000
??AND PATINDEX('%[ >]'+b.Station+'[-)]%',a.Line)=0
END
SELECT N'起點站 '=@Station_Start
?,N'終點站 '=@Station_Stop
?,N'乘車線路'=Line+N')'
FROM #
WHERE [Level]=@l
?AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到達的線路,則顯示處理結果表備查
?SELECT * FROM #
GO

--調用
EXEC p_qry N'站A',N'站L'
/*--結果
起點站? 終點站? 乘車線路
---------- ------------ -----------------------------------------------------------
站A??? 站L??? (8路: 站A->站B->站C->站D->站J->站L)
--*/

最短乘車路線查詢示例.sql