乘车路线查询

-- 模拟数据
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(4000),
    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 nvarchar(4000),
    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(4000),
           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(4000),
              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(4000),
              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

/*

path line_count state_count
3{1.广州东->2.体育西->3.珠江新城->4.客村}->2{5.客村->4.中大} 1 5
3{1.广州东->2.体育西}->1{3.体育西->4.烈士陵园->5.公园前}->2{3.公园前->4.中大} 2 6
1{1.广州东->2.体育中心->3.体育西->4.烈士陵园->5.公园前}->2{3.公园前->4.中大} 1 6
1{1.广州东->2.体育中心->3.体育西}->3{2.体育西->3.珠江新城->4.客村}->2{5.客村->4.中大} 2 7

*/