关闭

乘车线路查询

标签: pathinsertnulltable体育object
8641人阅读 评论(14) 收藏 举报
分类:
 
背景
有如下表示乘车线路和站点的数据,要求查询出指定站点之间的所有乘车线路:
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'广州东',UNION ALL
SELECT 1, N'体育中心',UNION ALL
SELECT 1, N'体育西',UNION ALL
SELECT 1, N'烈士陵园',UNION ALL
SELECT 1, N'公园前',UNION ALL
SELECT 1, N'西门口',UNION ALL
SELECT 2, N'火车站',UNION ALL
SELECT 2, N'纪念堂',UNION ALL
SELECT 2, N'公园前',UNION ALL
SELECT 2, N'中大',UNION ALL
SELECT 2, N'客村',UNION ALL
SELECT 2, N'琶洲',UNION ALL
SELECT 2, N'万胜围',UNION ALL
SELECT 3, N'广州东',UNION ALL
SELECT 3, N'体育西',UNION ALL
SELECT 3, N'珠江新城',UNION ALL
SELECT 3, N'客村',UNION ALL
SELECT 3, N'市桥',UNION ALL
SELECT 4, N'万胜围',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
 
 
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1280352次
    • 积分:14887
    • 等级:
    • 排名:第824名
    • 原创:178篇
    • 转载:9篇
    • 译文:0篇
    • 评论:881条
    最新评论