SQL from 语句中的 IF 子句

Question

我需要一个临时解决方案来解决我造成的问题。本质上我想计算两个值，但根据条件的结果使用不同的方法。

select userReturnval, userregisterid, OtherValue
FROM
(
   (SELECT otherValue
   FROM...
   ) as tblO  --unrelated table
  ,

  ( 
    if (select count(userregisterid) from table1 where site =@siteID and userid=@userID) >0
      SELECT userReturnval, userregisterid
    FROM
    (
     SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY       ...) as RANK
            FROM ...
            WHERE --first where clause
        ) as tblRank
        WHERE (RANK =1)
    else
      SELECT userReturnval, userregisterid
      FROM
        (
     SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY       ...) as RANK
            FROM ...
            WHERE --different where clause
        ) as tblRank
        WHERE (RANK =1)

  ) as tblR

我的 if 本身工作得很好，我只是为了让它作为更大查询的一部分工作。目前，sqlserver 不喜欢 if 存在其中。

希望有人能指出我正确的方向！

Answer 1

您可以尝试在 WHERE 子句中使用 case 语句，类似于下面的语句。请注意，我认为这对于性能来说并不是特别理想。

这样做确实可以让您将其保留为单个语句：

select userReturnval, userregisterid, OtherValue
FROM
(
   (SELECT otherValue
   FROM...
   ) as tblO  --unrelated table
  ,

  ( 
    SELECT userReturnval, userregisterid
    FROM
    (
     SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY       ...) as RANK
            FROM ...
            WHERE 
                case --Choose which where clause to use
                    when (select count(userregisterid) from table1 where site =@siteID and userid=@userID) >0 then 
                        case when /*First where clause*/ then 1
                        else 0
                        end
                    else
                        case when /*Second where clause*/ then 1
                        else 0
                        end
                    end
                 = 1                
        ) as tblRank
        WHERE (RANK =1)
  ) as tblR