CREATE TABLE T_Line(
ID????? nvarchar(10),? --公交線路號(hào)
Station nvarchar(10),? --站點(diǎn)名稱
Orders? int)?????????? --行車方向(通過它反應(yīng)每個(gè)站的上一個(gè)、下一個(gè)站)
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

--乘車線路查詢存儲(chǔ)過程
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'起點(diǎn)站 '=@Station_Start
?,N'終點(diǎn)站 '=@Station_Stop
?,N'乘車線路'=Line+N')'
FROM #
WHERE [Level]=@l
?AND Station=@Station_Stop
IF @@ROWCOUNT =0 --如果未有可以到達(dá)的線路,則顯示處理結(jié)果表備查
?SELECT * FROM #
GO

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

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