SQL数值倍率实现问题....

在以前的项目中遇到这么一个应用
按不同的数值来获取不同的倍率，然后再重得出一个值
为了定义方便我在程序中让用户自己定义一串倍率如下面的：
1.5:<=2.8;1.4:<=5.6;1.4:>2.8;1.3:<=8.4;1.3:>5.6;1.2:<=11.2;1.2:>8.4;1.1:<=16.8;1.1:>11.2;1.05:<=22.4;1.05:>16.8


说明一下其中的组成方式
　　1.5:<=2.8;
　当数值大于等于2.8时则返回1.5的倍率
　　格式：<倍率>:<比较符><比较值>;
               　用分号来做倍率设置分隔符

我是用SQL函数来实现的，但这个函数有很大的问题，还要改进，
在这函数中我先定义一张临时表，然后将倍率设置串转为数据表，
然后再在这张表中查找最匹配的倍率，但有对比较符有一个优先级
不知道大家做过类似的东西，给点建意:-)

create    function  GetRate( @tmpTestRate   nvarchar ( 2000 ), @tmpValue   float )
returns   float
as
begin

--  set nocount on 
--  declare @tmpTestRate nvarchar(2000)
--  declare @tmpValue float
--  set @tmpTestRate='1.5:<=2.8;1.4:<=5.6;1.4:>2.8;1.3:<=8.4;1.3:>5.6;1.2:<=11.2;1.2:>8.4;1.1:<=16.8;1.1:>11.2;1.05:<=22.4;1.05:>16.8'
--  set @tmpValue=12

declare   @rtlRate   float
declare   @tmpStr2   nvarchar ( 2000 )
declare   @r   table  (sql  nvarchar ( 2000 ),rate  float ,scale  float ,cmp  nvarchar ( 50 ))
declare   @tmpChar   nvarchar ( 3 )
declare   @tmpStr   nvarchar ( 2000 )
declare   @rate   float
declare   @scale   float
declare   @i   int
declare   @l   int
declare   @ex   bit
select   @i = 0 , @l = 0 , @tmpStr2 = '' , @scale = 0 , @tmpStr = ''
set   @tmpTestRate = ' ; ' + REPLACE ( @tmpTestRate , ' : ' , '  WHERE iPrcieX ' ) + ' ; '
set   @tmpTestRate = REPLACE ( @tmpTestRate , ' ; ' , ' | iWage=iPrice* ' )
set   @i = 1
set   @tmpTestRate = right ( @tmpTestRate , len ( @tmpTestRate ) - 1 )
while   len ( @tmpTestRate ) <> @i
begin
   set   @tmpChar = substring ( @tmpTestRate , @i , 1 )
   if   @tmpChar = ' | '   and   len ( @tmpStr ) > 1
   begin
     select   @ex = 0 , @l = len ( @tmpStr )
     while   @ex = 0   and   @l > 0
     begin
       set   @tmpChar = substring ( @tmpStr , @l , 1 )
       if   charindex ( @tmpChar , ' ~>=< ' ) <> 0
       begin
--        print @tmpStr
         select  
            @ex = 1 ,
            @scale = cast (
                     substring (
                       substring (
                         rtrim ( ltrim ( @tmpStr )),
                         1 ,
                         charindex ( '   ' , rtrim ( ltrim ( @tmpStr ))) - 1
                      ),
                       charindex ( ' * ' , rtrim ( ltrim ( @tmpStr ))) + 1 ,
                       len ( @tmpStr )
                    )  as   float
                  ),
            @rate = cast ( @tmpStr2   as   float ),
            @tmpStr2 = ''
           
       end
       else
         select   @tmpStr2 = @tmpChar + @tmpStr2
       set   @l = @l - 1
     end
     insert   into   @r  (sql,rate,scale,cmp)  values  ( @tmpStr , @rate , @scale , substring ( replace ( @tmpStr , @rate , '' ), charindex ( ' X ' , @tmpStr ) + 1 , len ( @tmpStr )))  
     set   @tmpStr = ''
   end
   else
     set   @tmpStr = @tmpStr + @tmpChar
   set   @i = @i + 1
end
set   @rtlRate = 0
select   top   1   @rtlRate = isnull (scale, 0 ), @i = isnull (rate, 0 )  from   @r   where   @tmpValue = rate  and  cmp = ' = '   order   by  rate
if   @rtlRate = 0
   select   top   1   @rtlRate = isnull (scale, 0 ), @i = isnull (rate, 0 )  from   @r    where   @tmpValue >= rate  and  cmp = ' >= '   order   by  rate
if   @rtlRate = 0
   select   top   1   @rtlRate = isnull (scale, 0 ), @i = isnull (rate, 0 )  from   @r    where   @tmpValue > rate  and  cmp = ' > '   order   by  rate
if   @rtlRate = 0
   select   top   1   @rtlRate = isnull (scale, 0 ), @i = isnull (rate, 0 )  from   @r    where   @tmpValue <= rate  and  cmp = ' <= '   order   by  rate
if   @rtlRate = 0
   select   top   1   @rtlRate = isnull (scale, 0 ), @i = isnull (rate, 0 )  from   @r    where   @tmpValue < rate  and  cmp = ' < '   order   by  rate


--  print @rtlRate
--  set nocount off

return   @rtlRate
end

转载于:https://www.cnblogs.com/pvistely/archive/2005/05/27/163534.html