乘车线路查询

 

背景

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

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'广州东', 1 UNION ALL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT 4, N'万胜围', 1 UNION 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.flag <> 0

       AND A.level = @level - 1

       AND A.current_lineID = B.lineID

       AND A.next_orderid = B.orderid

   

    UNION ALL

    -- 不同LineID

    SELECT

       path = CONVERT(nvarchar(max),

              A.path + N')->'

                  + RTRIM(B.lineID) + N'{'

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

           ),

       state_count = A.state_count + 1,

       line_count = A.line_count + 1,

       A.start_lineID, A.start_state,

       current_lineID = B.lineID,

       current_state = B.state,

       next_orderid = B.orderid,

       flag = CASE

              WHEN B.state = @state_stop THEN 0

              ELSE NULL END,

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

       level = - @level

    FROM #re A, #tb B

    WHERE A.flag <> 0

       AND state_count = @level - 1

       AND A.current_lineID <> B.lineID

       AND A.current_state = B.state

       AND NOT EXISTS(

              SELECT * FROM #re

              WHERE CHARINDEX(',' + RTRIM(B.lineID) + ',', A.lineIDs) > 0)

    SET @rows = @@ROWCOUNT

 

    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 + 1,

       flag = CASE

              WHEN B.state = @state_stop THEN 0

              ELSE 1 END,

       A.lineIDs,

       level = @level

    FROM #re A, #tb B

    WHERE A.flag IS NULL

       AND A.level = - @level

       AND A.current_lineID = B.lineID

       AND A.next_orderid + 1 = B.orderid

    UNION ALL

    -- 不同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 - 1,

       flag = CASE

              WHEN B.state = @state_stop THEN 0

              ELSE - 1 END,

       A.lineIDs,

       level = @level

    FROM #re A, #tb B

    WHERE A.flag IS NULL

       AND A.level = - @level

       AND A.current_lineID = B.lineID

       AND A.next_orderid - 1 = B.orderid

 

    SET @rows = @rows + @@ROWCOUNT

END

 

SELECT

-- *,

    path = path + N'}',

    line_count,

    state_count

FROM #re

WHERE flag = 0