T-SQL条件WHERE子句

admin

T-SQL条件WHERE子句

sql

在此找到了两个类似的问题，但无法弄清楚如何应用于我的方案。

我的函数有一个名为 @IncludeBelow 的参数。值是0或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，我需要查询如下：

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，则最后一行需要排除。（即不应用过滤器）。

我猜想它必须是一条CASE语句，但无法弄清楚语法。

这是我尝试过的：

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)

显然，这是不正确的。

正确的语法是什么？

阅读 235

2021-05-10

共1个答案

admin

我将查询更改为使用EXISTS，因为如果与POST相关联的位置不止一个，则将存在重复的POST记录，这些记录需要使用DISTINCT或GROUP
BY子句来消除…

不可燃

这将执行最坏的解决方案：

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)

可燃的非动态版本

自我解释…

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

2021-05-10