sql 查询 - 真 => 真，假 => 真或假
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1050007/
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 query - true => true, false => true or false
提问by Tabloo Quijico
Simple query, possibly impossible but I know there are some clever people out there :)
简单的查询，可能是不可能的，但我知道那里有一些聪明的人:)
Given a boolean parameter, I wish to define my where clause to either limit a certain column's output - or do nothing.
给定一个布尔参数，我希望定义我的 where 子句来限制某个列的输出 - 或者什么都不做。
So, given parameter @bit = 1 this would be the result:
因此，给定参数 @bit = 1 这将是结果：
where column = 1
其中列 = 1
given parameter @bit = 0 this would be the result:
给定参数@bit = 0 这将是结果：
where column = 1 or 0
其中列 = 1 或 0
i.e. have no effect/show all results (column is a bit field)
即没有效果/显示所有结果（列是位字段）
I'm not wanting dynamic sql - I can settle for fixing this in code but I just wondered if there's some clever magic that would make the above neat and simple.
我不想要动态 sql - 我可以解决在代码中解决这个问题，但我只是想知道是否有一些巧妙的魔法可以使上述内容简洁明了。
Is there? I'm using sql server.
在那儿？我正在使用 sql 服务器。
cheers :D
欢呼：D
回答by Adam Luter
The answer column = 1 or @bit = 0works if column may only be 0 or 1. If column may be any value you want: column = 1 or @bit = 0 and column = 0.
答案column = 1 or @bit = 0如果列只能是0或1。如果列可能是你想要的任何价值的作品： column = 1 or @bit = 0 and column = 0。
回答by Quassnoi
SELECT *
FROM mytable
WHERE column = 1 OR @bit = 0
If you have an index on column1, this one will be more efficient:
如果你有一个索引column1，这个索引会更有效：
SELECT *
FROM mytable
WHERE column = 1 AND @bit = 1
UNION ALL
SELECT *
FROM mytable
WHERE @bit = 0
See this article in my blog for performance comparison of a single WHEREcondition vs. UNION ALL:
有关单个WHERE条件与UNION ALL以下条件的性能比较，请参阅我博客中的这篇文章：
回答by A-K
where column BETWEEN @bit AND 1
回答by Rich
I had come up with a different answer and felt dumb when seeing the consensus answer. So, just for yucks, compared the two using my own database. I don't really know if they are really comparable, but my execution plans give a slight advantage to my goofy answer:
我想出了一个不同的答案，看到一致的答案时感到很愚蠢。所以，只是为了恶心，使用我自己的数据库比较了两者。我真的不知道它们是否真的具有可比性，但我的执行计划给我愚蠢的答案带来了一点优势：
select *
from MyTable
where column <> case @bit when 1 then 0 else -1 end
select *
from MyTable
where column <> case @bit when 1 then 0 else -1 end
I realize indices, table size, etc. can affect this.
Also, realized you probably can't compare a bit to a -1...
Just thought I'd share.
我意识到索引、表大小等会影响这一点。
此外，意识到你可能无法与 -1 进行比较......
只是想我会分享。
回答by eeeeaaii
try this
尝试这个
select ...
from table
where column = case when @bit = 0 then 0 else column end
this works no matter what the datatype of column is (could even be a string, for example). If it were, of course, it would be a different default value (not 0)
无论列的数据类型是什么，这都有效（例如，甚至可以是字符串）。如果是，当然，它将是不同的默认值（不是 0）
回答by gbn
WHERE column >= @bit
WHERE 列 >= @bit
However, this only works for > 0 values in a numeric column. @bit will be implicitly cast to int, smallint etc because of data type precedence.
但是，这仅适用于数字列中 > 0 的值。由于数据类型优先级，@bit 将被隐式转换为 int、smallint 等。
回答by Scott Ivey
select *
from MyTable
where (@bit = 0 OR MyColumn = 1)
回答by Martin Robins
select ...
from [table]
where @bit = 0 or (column = @bit)
