第一次发文章到首页,如果内容不好,请多见谅
由于项目需要,要在一个存储过程里建临时表和临时函数,来合并由一个存储过程综合了N个表的返回表,中间遇到了很多麻烦,现在终于弄好了。
先看最后的代码吧,一些注释在存储过程里有了:
CREATE PROCEDURE [dbo].[sp_ChangeStop_Second_Combination]
@StartStopName nvarchar(64),
@EndStopName nvarchar(64),
@TopNumber int
AS
BEGIN
SET NOCOUNT ON;
If object_id('TemptbChangeStop') is not null
Drop Table TemptbChangeStop
--通过sp_ChangeStop_Second_ResultByStopName存储过程,建立临时表
Create Table TemptbChangeStop
(
StartStopName nvarchar(64),
FirstLineName nvarchar(64),
FirstTotal int,
InterStopName nvarchar(64),
SecondLineName nvarchar(64),
SecondTotal int,
EndStopName nvarchar(64),
TotalStation int
)
Insert into TemptbChangeStop
--sp_ChangeStop_Second_ResultByStopName存储过程返回的表为未合并的表
Exec('sp_ChangeStop_Second_ResultByStopName '+@StartStopName+','+@EndStopName+',80')
Declare @FirstFunction nvarchar(1024)
Set @FirstFunction = 'Create Function dbo.f_ChangeStop_Combination1(@StartStopName nvarchar(64),@InterStopName nvarchar(64),@EndStopName nvarchar(64),@TotalStation int)
Returns nvarchar(256)
As
Begin
Declare @re varchar(256)
Set @Re = ''''
Select @Re=
--去掉经过的重复线路,注意:'',''+SecondLineName+''(''可以更精确的去掉重复线路,减少再一次的数据分拆和合并
Case When Charindex('',''+FirstLineName+''('',@Re)>0
Then @Re
Else @Re+'',''+FirstLineName+''(经过''+Cast(FirstTotal As varchar) + ''站)''
End
From TemptbChangeStop
Where StartStopName=@StartStopName And
InterStopName=@InterStopName And
EndStopName=@EndStopName And
TotalStation=@TotalStation
Return(Stuff(@Re,1,1,''''))
End'
--通过临时表,建立临时函数,合并第一次乘车线路和经过的站点条数
If object_id('f_ChangeStop_Combination1') is not null
Drop function f_ChangeStop_Combination1
--临时函数用Exec sp_executesql来建立
Exec sp_executesql @FirstFunction
-- Go
Declare @SecondFunction nvarchar(1024)
Set @SecondFunction = 'Create Function dbo.f_ChangeStop_Combination2(@StartStopName nvarchar(64),@InterStopName nvarchar(64),@EndStopName nvarchar(64),@TotalStation int)
Returns nvarchar(256)
As
Begin
Declare @re varchar(256)
Set @Re = ''''
Select @Re=
--去掉经过的重复线路,注意:'',''+SecondLineName+''(''可以更精确的去掉重复线路,减少再一次的数据分拆和合并
Case When Charindex('',''+SecondLineName+''('',@Re)>0
Then @Re
Else @Re+'',''+SecondLineName+''(经过''+Cast(SecondTotal As varchar) + ''站)''
End
From TemptbChangeStop
Where StartStopName=@StartStopName And
InterStopName=@InterStopName And
EndStopName=@EndStopName And
TotalStation=@TotalStation
Return(Stuff(@Re,1,1,''''))
End'
--通过临时表,建立临时函数,合并第二次乘车线路和经过的站点条数
If object_id('f_ChangeStop_Combination2') is not null
Drop function f_ChangeStop_Combination2
--临时函数用Exec sp_executesql来建立
Exec sp_executesql @SecondFunction
-- Go
--得到最终数据
Select Top(@TopNumber) StartStopName,FirstPassBy=dbo.f_ChangeStop_Combination1(StartStopName,InterStopName,EndStopName,TotalStation),InterStopName,SecondPassBy=dbo.f_ChangeStop_Combination2(StartStopName,InterStopName,EndStopName,TotalStation),EndStopName,TotalStation
From TemptbChangeStop
Group By StartStopName,InterStopName,EndStopName,TotalStation
Order By TotalStation
If object_id('f_ChangeStop_Combination1') is not null
Drop function f_ChangeStop_Combination1
If object_id('f_ChangeStop_Combination2') is not null
Drop function f_ChangeStop_Combination2
-- Go,这里用Go会出错的
If object_id('TemptbChangeStop') is not null
Drop Table TemptbChangeStop
END
执行未合并的存储过程的结果如下:
Exec('sp_ChangeStop_Second_ResultByStopName '+'新街口'+','+'新安江街'+',50')
合并后结果如下:
Declare @Time datetime
Set @Time = getdate();
Exec('sp_ChangeStop_Second_Combination 新街口,新安江街,10' )
Select datediff(ms,@Time,getdate())
平均执行300多到400多毫秒,时间上比较多,以后我还要优化。
如果各位有其它的方法,可以优化查询效率,希望告诉我下。
这个系统是南京公交查询系统,提供手机短信查询和WEB结合的查询,现在核心结构和核心代码都写好了,处理细节更花时间 :)
www.nj84.com和短信输入南京公交到12114查询(现在WEB还没怎么做好,功能也没放上去,以后用它会象GOOGLE那样提示的),感兴趣的欢迎和我交流。 :)
-
#1楼 aierong 2008-11-26 15:12
-
#2楼[楼主] Lawson 2008-11-26 15:23
表的合并方法也不是很多,或者用游标。
我的代码里,这个临时函数和临时表都必须得在这个存储过程里建,不是还不能实现。
你直接看这两个图吧,怎么把上面那个图弄成下面那个图。
直接能执行的SQL在存储过程里还必须得该很多小地方,浪费了我很多时间
对比这个图,如果有更好的方法实现,告诉我一下 :) -
#3楼 aierong 2008-11-26 15:30
CREATE TABLE tem
(
startstopname NVARCHAR(100) ,
FIRSTLinename NVARCHAR(100) ,
FIRSTTotal INT ,
secondlinename NVARCHAR(100)
)
INSERT INTO tem
(
startstopname ,
FIRSTLinename ,
FIRSTTotal ,
secondlinename
) SELECT '新街口' ,
'2路' ,
8 ,
'西街'
UNION ALL
SELECT '新街口' ,
'2路' ,
7 ,
'雨花台'
UNION ALL
SELECT '新街口' ,
'2路区间' ,
8 ,
'西街'
UNION ALL
SELECT '新街口' ,
'2路区间' ,
7 ,
'雨花台'
UNION ALL
SELECT '新街口' ,
'26路' ,
7 ,
'雨花台'
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION fun
(
-- Add the parameters for the function here
@startstopname NVARCHAR(100) ,
@secondlinename NVARCHAR(100)
)
RETURNS NVARCHAR(1000)
AS
BEGIN
-- Add the SELECT statement with parameter references here
DECLARE @result NVARCHAR(1000)
SELECT @result = ''
SELECT @result = @result + FIRSTLinename + '(经过'
+ CONVERT(NVARCHAR(100) , FIRSTTotal) + '站)' + ','
FROM tem
WHERE startstopname = @startstopname
AND secondlinename = @secondlinename
SELECT @result = LEFT(@result , LEN(@result) - 1)
RETURN @result
END
GO
SELECT startstopname ,
dbo.fun(startstopname , secondlinename) AS FIRSTPassBy ,
secondlinename
FROM tem
GROUP BY startstopname ,
secondlinename
GO
大概写了一段代码
不知道是否符合你的需要
仅仅提供参考
谢谢
要我实现不用那么麻烦
没必要什么动态建函数