邹大哥的存储过程 --示例数据
create table Line(lineID int,state nvarchar(10),orderid int,primary key(lineID,orderid))
insert Line select 1, '鼓楼 ' ,1
union all select 1, '新街口 ',2
union all select 1, '汽车站 ',3
union all select 1, '火车站 ',4
union all select 2, '新街口 ',1
union all select 2, '飞机场 ',2
union all select 2, '天安门 ',3
union all select 3, '天安门 ',1
union all select 3, '石门坎 ',2
union all select 3, '驾校 ' ,3
go --查询的存储过程
create proc p_qry
@begin_state nvarchar(10),
@end_state nvarchar(10)
as
set nocount on
declare @l int
set @l=0
select lineID,line=cast( '( '+rtrim(lineID)+ ': '+rtrim(state) as varchar(8000))
,state,orderid=orderid+1,level=@l,gid=1
into #t from Line where state=@begin_state
while @@rowcount> 0 and not exists(select * from #t where state=@end_state)
begin
set @l=@l+1
insert #t(line,lineID,state,orderid,level,gid)
select
line=a.line+case
when a.lineID=b.lineID
then '-> '+rtrim(b.state)
else ') => ( '+rtrim(b.lineID)+ ': '+rtrim(b.state)
end,
lineID=b.lineID,state=b.state,orderid=b.orderid+1,@l,
case when a.lineID=b.lineID then a.gid else a.gid+1 end
from #t a,Line b
where a.level=@l-1
and(
a.lineID=b.lineID and a.orderid=b.orderid
or
a.state=b.state and a.lineID <> b.lineID)
end
select 起点站=@begin_state
,终点站=@end_state
,转车次数=gid
,经过站数=case when gid <3 then @l else @l-gid+2 end
,乘车线路=line+ ') '
from #t where level=@l and state=@end_state
go --调用
exec p_qry '鼓楼 ', '火车站 '
exec p_qry '鼓楼 ', '飞机场 '
exec p_qry '鼓楼 ', '石门坎 '
go
转乘只能查到一条结果 那位高手帮改下 先谢谢了
create table Line(lineID int,state nvarchar(10),orderid int,primary key(lineID,orderid))
insert Line select 1, '鼓楼 ' ,1
union all select 1, '新街口 ',2
union all select 1, '汽车站 ',3
union all select 1, '火车站 ',4
union all select 2, '新街口 ',1
union all select 2, '飞机场 ',2
union all select 2, '天安门 ',3
union all select 3, '天安门 ',1
union all select 3, '石门坎 ',2
union all select 3, '驾校 ' ,3
go --查询的存储过程
create proc p_qry
@begin_state nvarchar(10),
@end_state nvarchar(10)
as
set nocount on
declare @l int
set @l=0
select lineID,line=cast( '( '+rtrim(lineID)+ ': '+rtrim(state) as varchar(8000))
,state,orderid=orderid+1,level=@l,gid=1
into #t from Line where state=@begin_state
while @@rowcount> 0 and not exists(select * from #t where state=@end_state)
begin
set @l=@l+1
insert #t(line,lineID,state,orderid,level,gid)
select
line=a.line+case
when a.lineID=b.lineID
then '-> '+rtrim(b.state)
else ') => ( '+rtrim(b.lineID)+ ': '+rtrim(b.state)
end,
lineID=b.lineID,state=b.state,orderid=b.orderid+1,@l,
case when a.lineID=b.lineID then a.gid else a.gid+1 end
from #t a,Line b
where a.level=@l-1
and(
a.lineID=b.lineID and a.orderid=b.orderid
or
a.state=b.state and a.lineID <> b.lineID)
end
select 起点站=@begin_state
,终点站=@end_state
,转车次数=gid
,经过站数=case when gid <3 then @l else @l-gid+2 end
,乘车线路=line+ ') '
from #t where level=@l and state=@end_state
go --调用
exec p_qry '鼓楼 ', '火车站 '
exec p_qry '鼓楼 ', '飞机场 '
exec p_qry '鼓楼 ', '石门坎 '
go
转乘只能查到一条结果 那位高手帮改下 先谢谢了
如 execute p_qry '新街口','鼓楼'
为测试是否支持多条转乘路线,特意在楼主原有的测试数据上增加了2路线的<新街站>
declare @line table (lineID int,state nvarchar(10),orderid int,primary key(lineID,orderid))
insert @Line select 1, '鼓楼' ,1
union all select 1, '新街口',2
union all select 1, '汽车站',3
union all select 1, '火车站',4
union all select 2, '新街口',1
union all select 2, '汽车站',2
union all select 2, '飞机场',3
union all select 2, '天安门',4
union all select 3, '天安门',1
union all select 3, '石门坎',2
union all select 3, '驾校',3
declare @b varchar(10),@e varchar(10)
set @b='石门坎' -- 出发站点
set @e='鼓楼' -- 目的站点;with l as (
select lineid,state,orderid,
cast('('+cast(lineid as varchar(10))+': '+state as varchar(max)) as path,
cast(cast(lineid as varchar(10))+state+',' as varchar(max)) as path_tmp,
0 as visible,1 as change,0 as state_count
from @line where state=@b
union all
select b.lineid,b.state,b.orderid,
cast(a.path+'->'+b.state as varchar(max)),
cast(a.path_tmp+cast(b.lineid as varchar(10))+b.state+',' as varchar(max)) as path_tmp,
1 as visible,a.change,a.state_count+1 as state_count
from l a
inner join @line b on a.lineid=b.lineid and abs(a.orderid-b.orderid)=1
and charindex(cast(b.lineid as varchar(10))+b.state,a.path_tmp)=0
union all
select b.lineid,b.state,b.orderid,
cast(a.path+') => ('+cast(b.lineid as varchar(10))+': '+b.state as varchar(max)),
cast(a.path_tmp+cast(b.lineid as varchar(10))+b.state+',' as varchar(max)) as path_tmp,
0 as visible,a.change+1 as change,a.state_count
from l a
inner join @line b on a.state=b.state and a.lineid<>b.lineid
and charindex(cast(b.lineid as varchar(10))+b.state,a.path_tmp)=0
where a.state_count>0
)
select path+')' as path,change,state_count
from l
where visible=1 and state=@e/*
path change state_count
-------------------------------------------------------------------------------- ----------- -----------
(3: 石门坎->天安门) => (2: 天安门->飞机场->汽车站) => (1: 汽车站->新街口->鼓楼) 3 5
(3: 石门坎->天安门) => (2: 天安门->飞机场->汽车站->新街口) => (1: 新街口->鼓楼) 3 5(所影响的行数为 2 行)
*/