pengdali(大力 V3.0)的函数执行速度不行
在txlicenhe(马可) 的函数基础上再添加点东西就比较好了:
create function Find_Max(@SourceNumber varchar(8000))
returns decimal(10,4)
as
begin
declare @fmax decimal(10,4)
set @fmax=(Select max(cast(F1 as decimal(10,4))) from (select * from dbo.f_splitstr(@sourceNumber,',')) aa)
return @fmax
end
go
print dbo.Find_Max('1,6,48.3596,-99.25')
create function getmax(@a varchar(8000))
returns int
as
begin
declare @ table (id int identity,a char(1))
declare @t int
insert @ select top 8000 null from sysobjects a,sysobjects b
select @t=max(cast(substring(','+@a,id+1,charindex(',',','+@a+',',id+1)-id-1) as int)) from @ where substring(','+@a,id,8000) like ',_%'
return @t
end
go
create proc getmax
@a varchar(200),
@c int output
as
declare @b nvarchar(4000)
set @b=N'select max(id) aa from (select '''+replace(@a,',',''' id union all select ''')+''') a'
exec sp_executesql @b,N'@aa int output',@c output
go
--调用:
declare @a int
exec getmax '1,2,34,56',@a output
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
-执行方式稍改一下:
Select max(cast(F1 as int)) from (select * from dbo.f_splitstr('2,3,5,8,15,45',',')) aa
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(F1 varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
-执行
然后就可以:
Select max(F1) from (select * from dbo.f_splitstr('2,3,5,8,15,45',',')) aa