用于计算返回数据结果的SQL语法

I am trying to perform a simple calculation to some data returned from SQL Server:

我试图对从SQL Server返回的一些数据执行简单的计算:

SELECT  val1X, val1Y, val2X, val2Y FROM myTable

I want to be able to calculate the following against the returned values and return just a single value - the result of the calculation below (this was originally written in VB6):

我希望能够根据返回的值计算以下内容并返回一个值 - 下面的计算结果(这最初是用VB6编写的):

 If IsNull(val1X) Or IsEmpty(val1X) Then val1X = 0
    If IsNull(val1Y) Or IsEmpty(val1Y) Then val1Y = 0
    If IsNull(val2X) Or IsEmpty(val2X) Then val2X = 0
    If IsNull(val2Y) Or IsEmpty(val2Y) Then val2Y = 0

    res1 = (val1X ^ 2) + (val1Y ^ 2)
    res2 = (val2X ^ 2) + (val2Y ^ 2)

    ResultVal = Sqr(IIf(res1 > res2, res1, res2))

Just wondering what the best way to do this would be?

只是想知道最好的方法是什么?

Thanks

1 个解决方案

#1

You can do it all in one statement in SQL, but it looks kinda ugly because you have to repeat chunks:

您可以在SQL中的一个语句中完成所有操作,但它看起来有点难看,因为您必须重复块:

SELECT 
  CASE WHEN 
    /* res1 */ Power(IsNull(val1X, 0), 2) + Power(IsNull(val1Y, 0), 2) 
    > /* res2 */ Power(IsNull(val2X, 0), 2) + Power(IsNull(val2Y, 0), 2)
  THEN
    /* res1 */ Power(IsNull(val1X, 0), 2) + Power(IsNull(val1Y, 0), 2)
  ELSE
    /* res2 */ Power(IsNull(val2X, 0), 2) + Power(IsNull(val2Y, 0), 2)
  END
FROM
  myTable

It'd be a little cleaner to use a user-defined function to wrap up that Power(IsNull(field, 0), 2) so you don't repeat yourself as much, but I'll leave that as an exercise for you to do. :)

使用用户定义的函数来包装Power(IsNull(field,0),2)会更加清晰,所以你不要重复自己,但我会把它作为练习给你去做。 :)

#1