SQL 帮助 - 基于 BIT 变量的条件 where 子句 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/626870/
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
SQL HELP - Conditional where clause based on a BIT variable - SQL Server
提问by mmattax
I need help writing a conditional where clause. here is my situation:
我需要帮助编写条件 where 子句。这是我的情况：
I have a bit value that determines what rows to return in a select statement. If the value is true, I need to return rows where the import_id column is not null, if false, then I want the rows where the import_id column is null.
我有一个位值，用于确定在 select 语句中返回哪些行。如果值为真，我需要返回 import_id 列不为空的行，如果为假，那么我想要 import_id 列为空的行。
My attempt at such a query (below) does not seem to work, what is the best way to accomplish this?
我对此类查询的尝试（如下）似乎不起作用，完成此操作的最佳方法是什么？
DECLARE @imported BIT
SELECT id, import_id, name FROM Foo WHERE
(@imported = 1 AND import_id IS NOT NULL)
AND (@imported = 0 AND import_is IS NULL)
Thanks.
谢谢。
回答by Lieven Keersmaekers
Change the ANDto OR
更改AND为OR
DECLARE @imported BIT
SELECT id, import_id, name FROM Foo WHERE
(@imported = 1 AND import_id IS NOT NULL)
OR (@imported = 0 AND import_is IS NULL)
Decomposing your original statement
分解你的原始陈述
you have essentially written
你基本上写了
@imported = 1
AND import_id IS NOT NULL
AND @imported = 0
AND import_is IS NULL
wich is equivalent to
相当于
@imported = 1 AND @imported = 0
AND import_id IS NOT NULL AND import_is IS NULL
what results in two pair of clauses that completely negate each other
什么导致两对子句完全否定对方
回答by sfossen
I think you meant
我想你的意思是
SELECT id, import_id, name FROM Foo WHERE
(@imported = 1 AND import_id IS NOT NULL)
OR (@imported = 0 AND import_is IS NULL)
^^^
回答by Amy B
Your query would require an OR to select between the different filters. It's better for the optimizer if you use separate queries in this case. Yes, code redundancy is bad, but to the optimizer these are radically different (and not redundant) queries.
您的查询需要一个 OR 来在不同的过滤器之间进行选择。如果在这种情况下使用单独的查询，对优化器来说会更好。是的，代码冗余是不好的，但是对于优化器来说，这些是完全不同的（而不是冗余的）查询。
DECLARE @imported BIT
IF @imported = 1
SELECT id, import_id, name
FROM Foo
WHERE import_id IS NOT NULL
ELSE
SELECT id, import_id, name
FROM Foo
WHERE import_id IS NULL
