数据的问题, 我的算法依赖于 orderid 来搜索下一站, 如果这个不连续, 则无法搜索下一站所以把数据改成下面的就行了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 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
1 广州东 1
1 体育中心2
1 体育西 3
1 烈士陵园4
1 公园前 6
1 西门口 7
2 火车站 1
2 纪念堂 2
2 公园前 3
2 中大 4
2 客村 5
2 琶洲 6
2 万胜围 7
3 广州东 1
3 体育西 2
3 珠江新城3
3 客村 4
3 市桥 5
4 万胜围 1
4 金洲 2
如上面数据,想查询“广州东”至“中大”,大家通过程序计算列出全部的方案。
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'金洲', 2DECLARE
@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
ENDSELECT
-- *,
path = path + N'}',
state_count
FROM @re
WHERE flag = 0
3{1.广州东->2.体育西->3.珠江新城->4.客村)->2{5.客村->4.中大} 5
1{1.广州东->2.体育中心->3.体育西)->3{2.体育西->3.珠江新城->4.客村)->2{5.客村->4.中大} 7
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'金洲', 2DECLARE
@state_start nvarchar(10),
@state_stop nvarchar(10)
SELECT
@state_start = N'广州东',
@state_stop = N'中大'-- 查询
DECLARE @re TABLE(
path nvarchar(max),
state_count int,
line_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,
line_count = 1,
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.line_count,
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,
line_count = A.line_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.line_count,
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.line_count,
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
ENDSELECT
-- *,
path = path + N'}',
line_count,
state_count
FROM @re
WHERE flag = 0
1{1.广州东->2.体育中心->3.体育西)->3{2.体育西->3.珠江新城->4.客村)->2{5.客村->4.中大} 3 7
还有一种就是 3{1.广州东-> 2.体育西->)->1(1.体育西->2.烈士陵园->3.公园前)->2(1.公园前->2.中大)
算起来一共应该有4种方法吧。感觉最后两个方法没算出来,应该是公园前->中大这路线没遍历。
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 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
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
你可以测试一下
1. 直接计算出 next_orderid, 而不是每次用 flag 去算, 这样可以提高 join 效率
2. 表变量改成临时表, 这样可以在相关的列上建立索引, 从而更快的与原表 join (当然, 原表相关的列上也要有索引)
你运用SQL语句真的是炉火纯青了,真令人羡慕。
先谢过了