为什么我不能在 select 语句中使用 if 语句？有人知道另一种方法吗？

Question

Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
Where a.Catkey = @Key
AND d.IntFlg = 1
If @color is not Null 
Begin
    AND c.UserFld1 = @color
End
Order By f.UOMID, e.SizeValue**

返回这个： 关键字“AND”附近的语法不正确。

Answer 1

尝试一下...

Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
Where a.Catkey = @Key
AND d.IntFlg = 1
AND (@color = c.UserFld1 OR @color is null)
Order By f.UOMID, e.SizeValue**

Answer 2

把它“翻过来”。您不能有条件地插入 AND，您必须始终说出 AND，然后您可以使用 if-else（或只是一个 OR，如其他答案）有条件地提供您想要的条件或只是 true/false。

Answer 3

您可以使用 if 语句来控制执行哪些语句，但不能使用它来删除部分查询。

不以这种方式构建的原因之一是查询的执行计划会根据输入的不同而不同，因此每次都必须创建一个新的执行计划并且无法重复使用。

有不同的方法可以处理这个问题，例如，如果变量为 null，则使用 isnull 函数将值与其自身进行比较：

AND c.UserFld1 = isnull(@color, c.UserFld1)

或者在比较之前检查是否为 null：

AND (@coor is null or c.UserFld1 = @color)

您始终可以将 if 放入code> 查询周围的语句，这将为您提供两个版本的查询，每个版本都有一个比运行查询时检查值稍好的计划。有时值得重复代码：

If @color is not Null Begin

  Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
  From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
  Where a.Catkey = @Key
    AND d.IntFlg = 1
    AND c.UserFld1 = @color
  Order By f.UOMID, e.SizeValue

End Else Begin

  Select b.ItemKey, a.CatKey, a.ParentKey, a.CatName, g.ItemID, c.UserFld1, c.UserFld3, c.UserFld5, f.UOMID, e.SizeValue
  From dbo.timWebCategory a Inner JOIN
    dbo.timWebCategoryItem b ON a.CatKey = b.CatKey Inner JOIN
    mas500_app.dbo.timItem c ON b.ItemKey = c.ItemKey Inner JOIN
    dbo.cpsIMItemDetails d ON c.ItemKey = d.ItemKey Inner JOIN
    dbo.timItemSize e ON c.ItemKey = e.ItemKey Left JOIN
    dbo.timVolumeUOM f ON e.VolUOMKey = f.VolUOMKey Left JOIN
    mas500_app.dbo.vWebItem g ON c.ItemKey = g.ItemKey
  Where a.Catkey = @Key
    AND d.IntFlg = 1
  Order By f.UOMID, e.SizeValue

End