关于网状查询的问题

Question

to happyflystone:
   老兄，有一条路径是1-2-3-4-5。
   在这个示例中，还要考虑避免循环的情况，如 1，2；3，2；3，1；构成了循环。
   我也曾考虑过将该数据集反向再和原数据集Union,这样就可以用一般的通用算法，但还是要考虑避免循环的问题，
   该问题我曾发表在Oracle社区中，但Oracle的回答不能让我满意，我只好寻求Sql的解法，再转换过去。
   注意：只能用在Sql2000中。

Answer 1

跟查找公交线路一样的道理。发篇老大的例子，-- 模拟数据
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