将数值裁剪到最小/最大边界的更简便方法

问题描述

是否有一种简单的方法来将值(由SQL select返回)限制到某些边界?

我有一些这样的表

Is there an easy way to restrict values - returned by a SQL select - to certain boundaries?

I''ve got a some tables like this

<br />dataTBL:<br />dt                id    x<br />2008-01-01 10:00  1      5.13<br />2008-01-01 10:00  2     14.00<br />2008-01-01 10:00  3     -2.10<br />2008-01-01 10:00  4      1.65<br />2008-01-01 11:00  1      5.19<br />...<br /><br />compTBL (balancing computation rules):<br />resultID   baseID   k     d     minX   maxX<br />100        1        +1.0   0    null   null<br />100        2        -0.5   0    +3     null<br />100        3        -0.5  +5    -10     0<br />200        1        +1.0   0    null   null<br />200        4        -1.0   10   0      100<br />...<br />

我想要计算ID 100，例如值

And I want to calculate for ID 100 e.g. the values

<br />   5.13 * 1.0 + 0.0 (no limits)<br />+ 14.00 *-0.5 + 0.0 (but at least 3)<br />+ -2.10 *-0.5 + 5.0 (but at least -10 and maximal 0)<br />

我正在使用Sql Server 2005，此刻我正在以这种方式进行操作

I''m working with Sql Server 2005 and at the moment I''m doing it this way

<br />select <br />  dt,<br />  resultID,<br />  sum(<br />    case <br />      when isnull(x,0) * k + d < minX then minX<br />      when isnull(x,0) * k + d > maxX then maxX<br />      else isnull(x,0) * k + d<br />    end<br />  )    <br />from dataTBL join compTBL on dataID = baseID<br />group by dt, resultID<br />

这很好，但我不知道"就像为了测试<，>而具有相同的 表达式 三次.还有.
有没有更短的方法来编写这种case-expression?还是还有其他我可以使用的功能?是否有类似"r = MaximumOf(MinimumOf(x，maxX)，minX)"的东西-甚至更好的是"r = ClipToBoundaries(x，minX，maxX)"?

感谢

Andy

This works fine, but I don''t like having the same expression three times just for testing <, > and else.
Is there no shorter way to write this case-expression? Or ist there some other function I could use? Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?

Thanks

Andy

推荐答案

ScruffR写道:

ScruffR wrote:

是否存在类似"r = MaximumOf(MinimumOf (x，maxX)，minX)"-甚至更好的是"r = ClipToBoundaries(x，minX，maxX)"?

Is there something like "r = MaximumOf(MinimumOf(x,maxX),minX)" - or even better "r = ClipToBoundaries(x,minX,maxX)"?

如何创建标量值函数ClipToBoundaries?

致谢，Syed Mehroz Alam

How about creating an scalar-valued function ClipToBoundaries?

Regards,
Syed Mehroz Alam

这篇关于将数值裁剪到最小/最大边界的更简便方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！