SQL 存储过程&算法_数据库技巧

r_name nvarchar(50),

r_type int

)

--直达

insert into #result

select c.* from t a,t b,t c where

a.lid=b.lid and a.[type]=b.[type] and a.id<b.id

and a.[name] = @name1 and b.[name] = @name2

and c.id>=a.id and c.id<=b.id order by c.id

if @@rowcount>0 begin

select * from #result

end

else begin

--换车

DECLARE @CurrenName NVARCHAR(50)

SET @CurrenName = @name1

change:

/*

--车次入栈

insert into #tmp (tmp_lid)

select distinct lid from t where [name] = @CurrenName

DECLARE @CurrenBus NVARCHAR(50)

SELECT TOP 1 @CurrenBus = tmp_lid FROM #tmp

*/

INSERT INTO #tmp1 (tmp1_name)

SELECT DISTINCT b.[name] FROM t a,t b WHERE a.[name] = @CurrenName AND b.lid = a.lid AND b.[name] <> @CurrenName

INSERT INTO #tmp (tmp_name)

select d.[tmp1_name] from t a,t b,t c, #tmp1 d where

a.lid=b.lid and a.[type]=b.[type] and a.id<b.id

and a.[name] = d.[tmp1_name] and b.[name] = @name2

and c.id>=a.id and c.id<=b.id

IF @@rowcount>0 BEGIN

select distinct c.* from t a,t b,t c,#tmp d where

a.lid=b.lid and a.[type]=b.[type] and a.id<b.id

and a.[name] = @name1 and b.[name] = d.tmp_name

and c.id>=a.id and c.id<=b.id order by c.id

select distinct c.* from t a,t b,t c,#tmp d where

a.lid=b.lid and a.[type]=b.[type] and a.id<b.id