用SQL创建最小/最大值?(复制)

Possible Duplicate:
Getting the minimum of two values in sql

可能的重复:在sql中获取两个值的最小值

I'm trying to get all values between a range. I'm doing something like this

我想要得到一个范围内的所有值。我正在做这样的事情。

DECLARE @StartSeq INT
set @StartSeq = (select Sequence from MyTbl where ...)

update ... and MyCol between @StartSeq and @StartSeq+@RelativePosition

But if RelativePosition is negative it fails because its a smaller amount then startseq. The easiest solution I can think of is duplicate my code and doing an if (RelPos<0) to see if I want to do @StartSeq+@RelativePosition first or second. However that doesn't seem like the best solution. Is there a way i can do it in sql? I tried min(a,b) but it doesnt work in sql.

但是如果相对位置是负的，它就会失败，因为它的数量更小，然后startseq。我能想到的最简单的解决方案是复制我的代码并执行if (RelPos<0)，以查看我是否希望首先执行@StartSeq+@RelativePosition。然而，这似乎不是最好的解决方案。有办法用sql来实现吗?我试过了min(a,b)，但是sql不行。

2 个解决方案

#1

something like this would do:

就像这样:

update ... and ((MyCol between @StartSeq and @StartSeq+@RelativePosition)
             or (MyCol between @StartSeq+@RelativePosition and @StartSeq))

alternately:

交替:

declare @MinSeq int
declare @MaxSeq int

select @MinSeq = min(Seq), @MaxSeq = max(Seq)
from (values (@StartSeq), (@StartSeq+@RelativePosition)) this (Seq)

update ... and MyCol between @MinSeq and @MaxSeq

#2

You can use a CASE statement in your between, E.g.

你可以在两者之间用一个CASE语句。

and MyCol Between CASE When @StartSeq <= @relativePosition Then @Start Else @RelativePosition End
               And CASE When @StartSeq <= @relativePosition Then @RelativePosition Else @Start End

it's rather nasty however, you should really try to ensure that the parameters make sense.

但是，你应该努力确保这些参数是合理的。

#1