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
我转换的MYsqlBEGINDECLARE state_start varchar(255) ; DECLARE state_stop varchar(255); DECLARE level int; DECLARE rows int default 2; SET @state_start='广州东'; SET @state_stop='中大'; SET @level = 0; DROP TABLE IF EXISTS `tb`; CREATE TABLE `tb` ( `lineID` INT NOT NULL default '0', `state` VARCHAR(255) default NULL, `orderid` int default '0' ) ;insert into tb values( 1,N'广州东',1); insert into tb values( 1,N'体育中心',2); insert into tb values( 1,N'体育西',3); insert into tb values( 1,N'烈士陵园',4); insert into tb values( 1,N'公园前',6); insert into tb values( 1,N'西门口',7); insert into tb values( 2, N'火车站', 1); insert into tb values( 2, N'纪念堂', 2); insert into tb values( 2, N'公园前', 3); insert into tb values( 2, N'中大', 4); insert into tb values( 2, N'客村', 5); insert into tb values( 2, N'琶洲', 6); insert into tb values( 2, N'万胜围', 7); insert into tb values(3, N'广州东', 1); insert into tb values(3, N'体育西', 2); insert into tb values(3, N'珠江新城', 3); insert into tb values(3, N'客村', 4); insert into tb values(3, N'市桥', 5); insert into tb values(4, N'万胜围', 1); insert into tb values(4, N'金洲', 2);DROP TABLE IF EXISTS `re`; CREATE TABLE `re` ( `path` varchar(255) NOT NULL default '0', `state_count` int default '0', `start_lineID` int default '0', `start_state` varchar(255) NOT NULL default '0', `current_lineID` int default '0', `current_state` varchar(255) NOT NULL default '0', `current_orderid` int default '0', `flag` int default '0', `lineIDs` varchar(255) NOT NULL default '0', `level` int default '0' ) ;insert into re() ( select CONCAT( A.lineID,"{",A.orderid,".",A.state), 0, A.lineID, A.state, A.lineID, A.state, A.orderid, case A.state when @state_stop then 0 else null end, CONCAT(',',A.lineID,","), -(@level+1) from tb A where A.state=@state_start );while @rows > 0 do begin -- 同一 LineID insert into re() ( select CONCAT(A.path,'->',B.orderid,'.',B.state), A.state_count + 1, A.start_lineID, A.start_state, B.lineID, B.state, B.orderid, case A.state when @state_stop then 0 else A.flag end, A.lineIDs, @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 ); -- 不同 LineID insert into re() ( select CONCAT(A.path,')->',B.lineID,'{',B.orderid,'.',B.state), A.state_count+1, A.start_lineID, A.start_state, B.lineID, B.state, B.orderid, case A.state when @state_stop then 0 else null end, concat(B.lineID,','), - @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 INSTR(',' + RTRIM(B.lineID) + ',', A.lineIDs) > 0) ); SET @rows = @ROWCOUNT; -- 不同 LineID 的第1站正向 insert into re() ( select CONCAT(A.path,'->',B.orderid,'.',B.state), A.state_count + 1, A.start_lineID, A.start_state, B.lineID, B.state, B.orderid, case A.state when @state_stop then 0 else 1 end, A.lineIDs, - @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 ); -- 不同 LineID 的第1站反向 insert into re() ( select CONCAT(A.path,'->',B.orderid,'.',B.state), A.state_count+1, A.start_lineID, A.start_state, B.lineID, B.state, B.orderid, case A.state when @state_stop then 0 else -1 end, A.lineIDs, @levell 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; end while; select path = path + N'}', state_count from re where flag =0; END
我搞一个刚开始的SQL 给你吧。create temporary table tb (lineID int, state nvarchar(10), orderid int); INSERT into 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;set @state_start = N'广州东'; set @state_stop = N'中大';-- 查询 create temporary table re ( path text, state_count int, start_lineID int, start_state nvarchar(10), current_lineID int, current_state nvarchar(10), current_orderid int, flag int, lineIDs text, `level` int );SET @level = 0; ... 以后的都一样。
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
DECLARE state_stop varchar(255);
DECLARE level int;
DECLARE rows int default 2; SET @state_start='广州东';
SET @state_stop='中大';
SET @level = 0;
DROP TABLE IF EXISTS `tb`;
CREATE TABLE `tb`
(
`lineID` INT NOT NULL default '0',
`state` VARCHAR(255) default NULL,
`orderid` int default '0'
) ;insert into tb values( 1,N'广州东',1);
insert into tb values( 1,N'体育中心',2);
insert into tb values( 1,N'体育西',3);
insert into tb values( 1,N'烈士陵园',4);
insert into tb values( 1,N'公园前',6);
insert into tb values( 1,N'西门口',7);
insert into tb values( 2, N'火车站', 1);
insert into tb values( 2, N'纪念堂', 2);
insert into tb values( 2, N'公园前', 3);
insert into tb values( 2, N'中大', 4);
insert into tb values( 2, N'客村', 5);
insert into tb values( 2, N'琶洲', 6);
insert into tb values( 2, N'万胜围', 7);
insert into tb values(3, N'广州东', 1);
insert into tb values(3, N'体育西', 2);
insert into tb values(3, N'珠江新城', 3);
insert into tb values(3, N'客村', 4);
insert into tb values(3, N'市桥', 5);
insert into tb values(4, N'万胜围', 1);
insert into tb values(4, N'金洲', 2);DROP TABLE IF EXISTS `re`;
CREATE TABLE `re`
(
`path` varchar(255) NOT NULL default '0',
`state_count` int default '0',
`start_lineID` int default '0',
`start_state` varchar(255) NOT NULL default '0',
`current_lineID` int default '0',
`current_state` varchar(255) NOT NULL default '0',
`current_orderid` int default '0',
`flag` int default '0',
`lineIDs` varchar(255) NOT NULL default '0',
`level` int default '0'
) ;insert into re()
(
select
CONCAT( A.lineID,"{",A.orderid,".",A.state),
0,
A.lineID,
A.state,
A.lineID,
A.state,
A.orderid,
case
A.state
when @state_stop
then 0
else null
end,
CONCAT(',',A.lineID,","),
-(@level+1)
from tb A
where A.state=@state_start
);while @rows > 0 do
begin -- 同一 LineID
insert into re()
(
select
CONCAT(A.path,'->',B.orderid,'.',B.state),
A.state_count + 1,
A.start_lineID,
A.start_state,
B.lineID,
B.state,
B.orderid,
case
A.state
when @state_stop
then 0
else A.flag
end,
A.lineIDs,
@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
); -- 不同 LineID
insert into re()
(
select
CONCAT(A.path,')->',B.lineID,'{',B.orderid,'.',B.state),
A.state_count+1,
A.start_lineID,
A.start_state,
B.lineID,
B.state,
B.orderid,
case
A.state
when @state_stop
then 0
else null
end,
concat(B.lineID,','),
- @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 INSTR(',' + RTRIM(B.lineID) + ',', A.lineIDs) > 0)
);
SET @rows = @ROWCOUNT;
-- 不同 LineID 的第1站正向
insert into re()
(
select
CONCAT(A.path,'->',B.orderid,'.',B.state),
A.state_count + 1,
A.start_lineID,
A.start_state,
B.lineID,
B.state,
B.orderid,
case
A.state
when @state_stop
then 0
else 1
end,
A.lineIDs,
- @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
);
-- 不同 LineID 的第1站反向
insert into re()
(
select
CONCAT(A.path,'->',B.orderid,'.',B.state),
A.state_count+1,
A.start_lineID,
A.start_state,
B.lineID,
B.state,
B.orderid,
case
A.state
when @state_stop
then 0
else -1
end,
A.lineIDs,
@levell
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;
end while;
select
path = path + N'}',
state_count
from re
where flag =0;
END
(lineID int, state nvarchar(10), orderid int);
INSERT into 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;set @state_start = N'广州东';
set @state_stop = N'中大';-- 查询
create temporary table re
(
path text,
state_count int,
start_lineID int,
start_state nvarchar(10),
current_lineID int,
current_state nvarchar(10),
current_orderid int,
flag int,
lineIDs text,
`level` int
);SET @level = 0;
...
以后的都一样。
那才是最简单的解决办法。