SQL 函数返回字符串中的最大值最小值(XML新解法)
SQL 函数返回字符串中的最大值最小值(XML新解法)
功能参数由指定的分隔符分隔值的集合(如 123,12,55,2222,90)
函数返回
集合中的最大值,最小值在收集和集合中的值的数目。
/*
例:
运行下面的函数
select* from dbo.GetMaxandMinValue('2,22,5,1,88,2000,7897,800',',')
得到这样的结果
minValue maxValue CountOfNumberSupplied
-------------------------------------------------------------------
1.007897.00 8
*/
if object_id('GetMaxandMinValue','F') is not null drop function GetMaxandMinValue
go
create functionGetMaxandMinValue(@valuescollection varchar(max),@separator char(1))
returns @table table(minvalue numeric(20,2),maxvalue numeric(20,2),countofnumbersupplied int)
as
begin
declare @x xml
declare @minnumber varchar(50)
declare @maxnumber varchar(50)
declare @numbercount varchar(20)
select @x='<number>' + replace(@valuescollection,@separator,'</number><number>')+ '</number>'
select @minnumber= convert(varchar(50),@x.query('fn:min(number)') )
select @maxnumber= convert(varchar(50),@x.query('fn:max(number)'))
select @numbercount= convert(varchar(50),@x.query('fn:count(number)'))
insert into @table select cast(@minnumber as float),cast(@maxnumber as float),cast(@numbercount as int)
return;
end
go
select * from dbo.GetMaxandMinValue('2,22,5,1,88,2000,7897,800',',')
评论暂时关闭