queryline.txt

查询公交线路情况

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[line_query]
 @state_start nvarchar(10), @state_stop nvarchar(10)
as  
declare @line_count int
-- 查询
IF OBJECT_ID(N'#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 varchar(20),
    start_state nvarchar(10),
    current_lineID varchar(20),
    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'{' + A.state 
              --+ RTRIM(A.orderid) + N'.' 
       ),
    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 like @state_stop+'%' THEN 0
           ELSE NULL END,
    lineIDs = ',' + RTRIM(A.lineID) + ',',
    level = -(@level + 1)
FROM table_view A
WHERE state like @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'->'+ B.state 
                 -- + RTRIM(B.orderid) + N'.' 
           ),
       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 like @state_stop+'%' THEN 0
              ELSE A.flag END,
       A.lineIDs,
       level = @level
    FROM #re A, table_view 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'{' + B.state 
                 -- + RTRIM(B.orderid) + N'.' 
           ),
       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 like @state_stop+'%' THEN 0
              ELSE NULL END,
       A.lineIDs + RTRIM(B.lineID) + ',',
       level = - @level
    FROM #re A, table_view B
    WHERE A.flag <> 0
       AND state_count = @level - 1
       AND A.current_lineID <> B.lineID
       AND A.current_state = B.state
       AND A.state_count<4
       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'->'+ B.state 
                  --+ RTRIM(B.orderid) + N'.' 
           ),
       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 like @state_stop+'%' THEN 0
              ELSE 1 END,
       A.lineIDs,
       level = @level
    FROM #re A, table_view B
    WHERE A.flag IS NULL
       AND A.level = - @level
       AND A.current_lineID = B.lineID
       AND A.next_orderid + 1 = B.orderid
       AND A.state_count<4
    UNION ALL
    -- 不同LineID 的第站反向
    SELECT 
       path = CONVERT(nvarchar(max), 
              A.path 
                  + N'->'+ B.state 
                 -- + RTRIM(B.orderid) + N'.' 
           ),
       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 like @state_stop+'%' THEN 0
              ELSE - 1 END,
       A.lineIDs,
       level = @level
    FROM #re A, table_view B
    WHERE A.flag IS NULL
       AND A.level = - @level
       AND A.current_lineID = B.lineID
       AND A.next_orderid - 1 = B.orderid
       AND A.state_count<4
    SET @rows = @rows + @@ROWCOUNT
select @line_count=count(*)from #re where line_count>3
if(@line_count>0)
break
END
SELECT path = path + '}', line_count, state_count,flag FROM #re 
WHERE flag = 0 order by line_count