DECLARE @tb TABLE( lineID int, state nvarchar(10), orderid int) INSERT @tb 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'公园前', 6 UNION ALL SELECT 1, N'西门口', 7 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
DECLARE @state_start nvarchar(10), @state_stop nvarchar(10) SELECT @state_start = N'广州东', @state_stop = N'中大'
-- 查询 DECLARE @re TABLE( path nvarchar(max), state_count int, start_lineID int, start_state nvarchar(10), current_lineID int, current_state nvarchar(10), current_orderid int, flag int, lineIDs nvarchar(max), level int ) DECLARE @level int, @rows int SET @level = 0
-- 开始 INSERT @re SELECT path = CONVERT(nvarchar(max), RTRIM(A.lineID) + N'{' + RTRIM(A.orderid) + N'.' + A.state ), state_count = 0, start_lineID = A.lineID, start_state = A.state, current_lineID = A.lineID, current_state = A.state, current_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 -- 同一 LineID SELECT path = CONVERT(nvarchar(max), A.path + N'->' + RTRIM(B.orderid) + N'.' + B.state ), state_count = A.state_count + 1, A.start_lineID, A.start_state, current_lineID = B.lineID, current_state = B.state, current_orderid = B.orderid, 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.current_orderid + A.flag = 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, A.start_lineID, A.start_state, current_lineID = B.lineID, current_state = B.state, current_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 -- 不同 LineID 的第1站正向 SELECT path = CONVERT(nvarchar(max), A.path + N'->' + RTRIM(B.orderid) + N'.' + B.state ), state_count = A.state_count + 1, A.start_lineID, A.start_state, current_lineID = B.lineID, current_state = B.state, current_orderid = B.orderid, 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.current_orderid + 1 = B.orderid UNION ALL -- 不同 LineID 的第1站反向 SELECT path = CONVERT(nvarchar(max), A.path + N'->' + RTRIM(B.orderid) + N'.' + B.state ), state_count = A.state_count + 1, A.start_lineID, A.start_state, current_lineID = B.lineID, current_state = B.state, current_orderid = B.orderid, 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.current_orderid - 1 = B.orderid
SET @rows = @rows + @@ROWCOUNT END
SELECT -- *, path = path + N'}', state_count FROM @re WHERE flag = 0
|