乘车线路查询

背景

有如下表示乘车线路和站点的数据，要求查询出指定站点之间的所有乘车线路：

USE tempdb

GO

-- 模拟数据

SET NOCOUNT ON

IF OBJECT_ID(N'tempdb..#tb') IS NOT NULL

DROP TABLE #tb

CREATE TABLE #tb(

id int IDENTITY

PRIMARY KEY,

lineID int,

state nvarchar(10),

orderid int

)

INSERT #tb(

lineID, state, orderid)

SELECT 1, N'广州东', 1UNION ALL

SELECT 1, N'体育中心', 2UNION ALL

SELECT 1, N'体育西', 3UNION ALL

SELECT 1, N'烈士陵园', 4UNION ALL

SELECT 1, N'公园前', 5UNION ALL

SELECT 1, N'西门口', 6UNION ALL

SELECT 2, N'火车站', 1UNION ALL

SELECT 2, N'纪念堂', 2UNION ALL

SELECT 2, N'公园前', 3UNION ALL

SELECT 2, N'中大', 4UNION ALL

SELECT 2, N'客村', 5UNION ALL

SELECT 2, N'琶洲', 6UNION ALL

SELECT 2, N'万胜围', 7UNION ALL

SELECT 3, N'广州东', 1UNION ALL

SELECT 3, N'体育西', 2UNION ALL

SELECT 3, N'珠江新城', 3UNION ALL

SELECT 3, N'客村', 4UNION ALL

SELECT 3, N'市桥', 5UNION ALL

SELECT 4, N'万胜围', 1UNION ALL

SELECT 4, N'金洲', 2

CREATE INDEX IX_lineID

ON #tb(

lineID)

CREATE INDEX IX_state

ON #tb(

state)

CREATE INDEX IX_orderid

ON #tb(

orderid)

GO

处理方法：

之前也有发表过一些如何处理这个问题的方法，但效率不是太好。下面的这种方法加上了乘车方向的考虑：同一条线路上，只有两个乘车方向，而且一旦方向了，就不会再反向乘车（因为是从这个方向来，再坐回去是不合理的）；如果某个站点可以换到另一条线路，则换乘后的另一条线路也是两个方向乘车。通过乘车方向的控制，减少了算法要搜索的路径。

-- 乘车路线查询

DECLARE

@state_start nvarchar(10),

@state_stop nvarchar(10)

SELECT

@state_start = N'广州东',

@state_stop = N'中大'

-- 查询

IF OBJECT_ID(N'tempdb..#re') IS NOT NULL

DROP TABLE #re

CREATE TABLE #re(

ID int IDENTITY

PRIMARY KEY,

path nvarchar(max),

state_count int,

line_count int,

start_lineID int,

start_state nvarchar(10),

current_lineID int,

current_state nvarchar(10),

next_orderid int,

flag int,

lineIDs varchar(max),

level int

)

CREATE INDEX IX_current_lineID

ON #re(

current_lineID)

CREATE INDEX IX_current_state

ON #re(

current_state )

CREATE INDEX IX_next_orderid

ON #re(

next_orderid )

CREATE INDEX IX_current_level

ON #re(

level )

DECLARE

@level int,

@rows int

SET

@level = 0

-- 开始

INSERT #re(

path,

state_count, line_count,

start_lineID, start_state,

current_lineID, current_state,

next_orderid, flag, lineIDs, level)

SELECT

path = CONVERT(nvarchar(max),

RTRIM(A.lineID) + N'{'

+ RTRIM(A.orderid) + N'.' + A.state

),

state_count = 0,

line_count = 0,

start_lineID = A.lineID,

start_state = A.state,

current_lineID = A.lineID,

current_state = A.state,

next_orderid = A.orderid,

flag = CASE

WHEN A.state = @state_stop THEN 0

ELSE NULL END,

lineIDs = ',' + RTRIM(A.lineID) + ',',

level = -(@level + 1)

FROM #tb A

WHERE state = @state_start

SET @rows = @@ROWCOUNT

WHILE @rows > 0

BEGIN

SELECT

@level = @level + 1

INSERT #re(

path,

state_count, line_count,

start_lineID, start_state,

current_lineID, current_state,

next_orderid, flag, lineIDs, level)

-- 同一LineID

SELECT

path = CONVERT(nvarchar(max),

A.path

+ N'->'

+ RTRIM(B.orderid) + N'.' + B.state

),

state_count = A.state_count + 1,

A.line_count,

A.start_lineID, A.start_state,

current_lineID = B.lineID,

current_state = B.state,

next_orderid = B.orderid + A.flag,

flag = CASE

WHEN B.state = @state_stop THEN 0

ELSE A.flag END,

A.lineIDs,

level = @level

FROM #re A, #tb B

WHERE A