SQL 存储过程&算法

/****** Object:  Stored Procedure dbo.Search    Script Date: 2005-9-8 10:28:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N''''[dbo].[Search]'''') and OBJECTPROPERTY(id, N''''IsProcedure'''') = 1)
drop procedure [dbo].[Search]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object:  Stored Procedure dbo.Search    Script Date: 2005-9-8 10:28:35 ******/
CREATE proc Search
@name1 nvarchar(50),
@name2 nvarchar(50)
as

--中转站
create table #tmp
(
 tmp_id int identity(1,1),
 tmp_name NVARCHAR(50)
)

--站点队列
create table #tmp1
(
 tmp1_id int identity(1,1),
 tmp1_name NVARCHAR(50)
)

--查找结果
create table #result
(
 r_id int,
 r_lid nvarchar(50),
 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
   and a.[name] = d.tmp_name and b.[name] = @name2
   and c.id>=a.id and c.id<=b.id order by c.id
  END
  --SELECT * FROM #tmp
end

drop table #result
drop table #tmp1
drop table #tmp
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

exec search ''''文一路口'''',''''总管塘''''

上一页  [1] [2] 

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18