SQL Where 子句中的条件运算符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2568775/
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
Conditional Operator in SQL Where Clause
提问by Marc
I'm wishing I could do something like the following in SQl Server 2005 (which I know isnt valid) for my where clause. Sometimes @teamID (passed into a stored procedure) will be the value of an existing teamID, otherwise it will always be zero and I want all rows from the Team table.
我希望我可以在 SQl Server 2005（我知道这是无效的）中为我的 where 子句执行以下操作。有时@teamID（传递到存储过程中）将是现有 teamID 的值，否则它将始终为零，我想要 Team 表中的所有行。
I researched using Case and the operator needs to come before or after the entire statement which prevents me from having a different operator based on the value of @teamid. Any suggestions other than duplicating my select statements.
我使用 Case 进行了研究，并且运算符需要在整个语句之前或之后出现，这会阻止我根据 @teamid 的值使用不同的运算符。除了复制我的选择语句之外的任何建议。
declare @teamid int
set @teamid = 0
Select Team.teamID From Team
case @teamid
when 0 then
WHERE Team.teamID > 0
else
WHERE Team.teamID = @teamid
end
回答by Andomar
You can do that without a case:
你可以在没有案例的情况下做到这一点：
SELECT Team.teamID
FROM Team
WHERE (@teamid = 0 AND Team.teamID > 0)
OR (@teamid <> 0 AND Team.teamID = @teamid)
回答by OMG Ponies
Without using dynamic SQL, the most performant option is:
在不使用动态 SQL 的情况下，性能最高的选项是：
IF @teamid = 0
BEGIN
SELECT t.teamid
FROM TEAM t
WHERE t.teamid > 0
END
ELSE
BEGIN
SELECT t.teamid
FROM TEAM t
WHERE t.teamid = @teamid
END
Using Dynamic SQL:
使用动态 SQL：
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT t.teamid
FROM TEAM t
WHERE 1 = 1 '
SET @SQL = @SQL + CASE @teamid
WHEN 0 THEN ' AND t.teamid > 0 '
ELSE ' AND t.teamid = @teamid '
END
BEGIN
EXEC sp_EXECUTESQL @SQL N'@teamid INT', @teamid
END
Beware that sp_EXECUTESQLcaches the query plan, while EXEC will not. Read this: http://www.sommarskog.se/dynamic_sql.html
注意sp_EXECUTESQL缓存查询计划，而 EXEC 不会。阅读：http: //www.sommarskog.se/dynamic_sql.html
回答by Program.X
What about:
关于什么：
Select Team.teamID From Team Where (@teamid=0 and team.teamID>0) or (@teamid<>0 and team.teamid=@teamid)
回答by tpdi
Even simpler than Andomar's answer, and assuming that id is never 0 (as for most auto-increment ids) is
甚至比 Andomar 的答案更简单，假设 id 从不为 0（对于大多数自动递增 id）是
SELECT Team.teamID
FROM Team
WHERE @teamid = 0 or Team.teamID = @teamid;
That predicate is always true when @teamid is zero, and otherwise only true when it matches a particular row's teamId.
当@teamid 为零时，该谓词始终为真，否则仅当它与特定行的 teamId 匹配时才为真。
Note however: this works pretty efficiently on Sybase 11 or above; it worked pretty inefficiently on MS SQL server 2003; I don't know how it works on the current version of MS SQL Server.
但是请注意：这在 Sybase 11 或更高版本上非常有效；它在 MS SQL Server 2003 上的工作效率很低；我不知道它在当前版本的 MS SQL Server 上是如何工作的。
Also, you can use case; you just have to put the case in where clause, not the where clause in the case. So your original query would be:
此外，您可以使用案例；你只需要把 case 放在 where 子句中，而不是 case 中的 where 子句。所以你的原始查询是：
Select Team.teamID
From Team
where
case when @teamid = 0 then 0 else Team.teamID end = @teamId;
Note that this is likely to be less efficient, however, as it must be evaluated per row and will also likely result in a full table scan.
请注意，这可能效率较低，因为它必须按行进行评估，并且还可能导致全表扫描。
The form I gave above is more likely to be rewritten by a smart query optimizer (your mileage depends on your RDBMS) to use an index when @teamid is not zero.
我上面给出的表单更有可能被智能查询优化器重写（你的里程取决于你的 RDBMS）以在 @teamid 不为零时使用索引。
回答by JeffO
If you could treat Null as all records:
如果您可以将 Null 视为所有记录：
WHERE Team.teamID = ISNULL(@teamid, Team.teamID)
