T-SQL 条件 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4485965/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Conditional WHERE Clause
提问by RPM1984
Found a couple of similar questions here on this, but couldn't figure out how to apply to my scenario.
在这里找到了几个类似的问题，但无法弄清楚如何应用于我的场景。
My function has a parameter called @IncludeBelow. Values are 0 or 1 (BIT).
我的函数有一个名为@IncludeBelow的参数。值为 0 或 1 (BIT)。
I have this query:
我有这个查询：
SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue
If @IncludeBelow is 0, i need the query to be this:
如果@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.
If @IncludeBelow is 1, that last line needs to be excluded. (i.e don't apply filter).
如果@IncludeBelow 为 1，则需要排除最后一行。（即不应用过滤器）。
I'm guessing it needs to be a CASEstatement, but can't figure out the syntax.
我猜它需要是一个CASE语句，但无法弄清楚语法。
Here's what i've tried:
这是我尝试过的：
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)
Obviously that's not correct.
显然这是不正确的。
What's the correct syntax?
什么是正确的语法？
回答by OMG Ponies
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...
我将查询更改为使用 EXISTS，因为如果有多个位置与 POST 相关联，则会有重复的 POST 记录需要 DISTINCT 或 GROUP BY 子句来摆脱...
The non-sargable
不可交易的
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)
The sargable, non-dynamic version
sargable 非动态版本
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
The sargable, dynamic version (SQL Server 2005+):
sargable 动态版本（SQL Server 2005+）：
Love or hate it, dynamic SQL lets you write the query once. Just be aware that sp_executesql caches the query plan, unlike EXEC in SQL Server. Highly recommend reading The Curse and Blessings of Dynamic SQLbefore considering dynamic SQL on SQL Server...
不管喜欢还是讨厌，动态 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
回答by Rup
You can write it as
你可以把它写成
SELECT p.*
FROM Locations l
INNER JOIN Posts p
ON l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue
AND ((@IncludeBelow = 1) OR (p.LocationTypeId = @LocationType))
which is a pattern you see a lot e.g. for optional search parameters. But IIRC that can mess up the query execution plans so there may be a better way to do this.
这是您经常看到的一种模式，例如可选搜索参数。但是 IIRC 可能会弄乱查询执行计划，因此可能有更好的方法来做到这一点。
Since it's only a bit, it almost might be worth deciding between two blocks of SQL with or without the check, e.g. using an IF in a stored procedure or with different command strings in calling code, based on the bit?
由于它只是一点点，几乎可能值得在有或没有检查的两个 SQL 块之间做出决定，例如在存储过程中使用 IF 或在调用代码中使用不同的命令字符串，基于位？
回答by Hai Phan
You can change your CASEstatement to this. The query planner sees this differently, but it may be no more efficient than using OR:
您可以将您的CASE声明更改为此。查询规划器对此有不同的看法，但它可能并不比使用 OR 更有效：
(p.LocationTypeId = CASE @IncludeBelow WHEN 0 THEN p.LocationTypeId ELSE @LocationType END)
回答by Rich Capp
Edit the sql statement as follows:
编辑sql语句如下：
SELECT p.*
FROM Locations l
INNER JOIN Posts p
on l.LocationId = p.LocationId
WHERE l.Condition1 = @Value1
AND l.SomeOtherCondition = @SomeOtherValue
AND l.LocationType like @LocationType
The @IncludeBelow variable is not needed
不需要@IncludeBelow 变量
To include all location types Set @LocationType = '%'
包含所有位置类型 Set @LocationType = '%'
To limit the location types returned by the query Set @LocationType = '[A Specific Location Type]'
限制查询返回的位置类型 Set @LocationType = '[A Specific Location Type]'
The above Set statements assume the @LocationType variable is character datatype
上面的 Set 语句假设 @LocationType 变量是字符数据类型
