T-SQL 条件 WHERE 子句 [英] T-SQL Conditional WHERE Clause

问题描述

在这里找到了几个类似的问题，但不知道如何应用到我的场景中.

Found a couple of similar questions here on this, but couldn't figure out how to apply to my scenario.

我的函数有一个名为 @IncludeBelow 的参数.值为 0 或 1 (BIT).

My function has a parameter called @IncludeBelow. Values are 0 or 1 (BIT).

我有这个查询:

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue

如果@IncludeBelow 是 0，我需要查询是这样的:

If @IncludeBelow is 0, i need the query to be this:

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue
AND   p.LocationType = @LocationType -- additional filter to only include level.

如果@IncludeBelow 为 1，则需要排除最后一行.(即不应用过滤器).

If @IncludeBelow is 1, that last line needs to be excluded. (i.e don't apply filter).

我猜它需要是一个 CASE 语句，但无法弄清楚语法.

I'm guessing it needs to be a CASE statement, but can't figure out the syntax.

这是我尝试过的:

SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND   l.SomeOtherCondition = @SomeOtherValue
AND (CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId = @LocationType ELSE 1 = 1)

显然这是不正确的.

正确的语法是什么?

推荐答案

我将查询更改为使用 EXISTS，因为如果有多个位置与 POST 相关联，则会有重复的 POST 记录需要 DISTINCT 或GROUP BY 子句摆脱...

I changed the query to use EXISTS because if there's more than one location associated with a POST, there'd be duplicate POST records that'd require a DISTINCT or GROUP BY clause to get rid of...

这将执行最坏的可能解决方案:

This will perform the worst of the possible solutions:

SELECT p.*
  FROM POSTS p
 WHERE EXISTS(SELECT NULL
                FROM LOCATIONS l
               WHERE l.LocationId = p.LocationId
                 AND l.Condition1 = @Value1
                 AND l.SomeOtherCondition = @SomeOtherValue)
   AND (@IncludeBelow = 1 OR p.LocationTypeId = @LocationType)

sargable 非动态版本

不言自明....

The sargable, non-dynamic version

Self explanitory....

BEGIN
  IF @IncludeBelow = 0 THEN
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue)
       AND p.LocationTypeId = @LocationType
  ELSE
    SELECT p.*
      FROM POSTS p
     WHERE EXISTS(SELECT NULL
                    FROM LOCATIONS l
                   WHERE l.LocationId = p.LocationId
                     AND l.Condition1 = @Value1
                     AND l.SomeOtherCondition = @SomeOtherValue) 
END

可调整的动态版本(SQL Server 2005+):

喜欢或讨厌它，动态 SQL 可让您编写一次查询.请注意 sp_executesql 缓存查询计划，这与 SQL Server 中的 EXEC 不同.强烈建议在考虑 SQL Server 上的动态 SQL 之前阅读动态 SQL 的诅咒和祝福...

DECLARE @SQL VARCHAR(MAX)
    SET @SQL = 'SELECT p.*
                  FROM POSTS p
                 WHERE EXISTS(SELECT NULL
                                FROM LOCATIONS l
                               WHERE l.LocationId = p.LocationId
                                 AND l.Condition1 = @Value1
                                 AND l.SomeOtherCondition = @SomeOtherValue)'

    SET @SQL = @SQL + CASE 
                        WHEN @IncludeBelow = 0 THEN
                         ' AND p.LocationTypeId = @LocationType '
                        ELSE ''
                      END   

BEGIN 

  EXEC sp_executesql @SQL, 
                     N'@Value1 INT, @SomeOtherValue VARCHAR(40), @LocationType INT',
                     @Value1, @SomeOtherValue, @LocationType

END

这篇关于T-SQL 条件 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！