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