在 SQL 查询中将 int 或 null 转换为布尔值的最佳方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/177956/
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
What is the best way to convert an int or null to boolean value in an SQL query?
提问by Thomas Bratt
What is the best way to convert an int or null to boolean value in an SQL query, such that:
在 SQL 查询中将 int 或 null 转换为布尔值的最佳方法是什么，例如：
- Any non-null value is TRUEin the results
- Any null value is FALSEin the results
- 任何非空值为TRUE的结果
- 任何空值是假的结果
回答by Tomalak
To my knowledge (correct me if I'm wrong), there is no concept of literal boolean values in SQL. You can have expressions evaluating to boolean values, but you cannot output them.
据我所知（如果我错了，请纠正我），SQL 中没有文字布尔值的概念。您可以将表达式计算为布尔值，但不能输出它们。
This said, you can use CASE WHEN to produce a value you can use in a comparison:
这就是说，您可以使用 CASE WHEN 来生成可用于比较的值：
SELECT
CASE WHEN ValueColumn IS NULL THEN 'FALSE' ELSE 'TRUE' END BooleanOutput
FROM
table
回答by cvk
No need to use case... when:
不需要用例...当：
select (column_name is not null) as result from table_name;
Returns 1 for all fields not NULL and 0 for all fields that are NULL, which is as close as you can get to booleans in SQL.
为所有非 NULL 字段返回 1，为所有 NULL 字段返回 0，这与 SQL 中的布尔值最接近。
回答by mattlant
SELECT
CASE
WHEN thevalue IS NULL THEN 0
ELSE 1
END AS newVal
FROM .... (rest of select)
I think it goes something like this
我认为它是这样的
Actually, the ISNULL, may need to be WHEN thevalue IS NULL THEN 0
实际上，ISNULL，可能需要是 WHEN thevalue IS NULL THEN 0
回答by kristof
Assuming you want 0,1 value as a return, and that we are talking about integer I would use the logic specified by Torbj?rnand wrap it in the function
假设您想要 0,1 值作为返回值，并且我们正在讨论整数，我将使用Torbj?rn指定的逻辑并将其包装在函数中
create function dbo.isNotNull(@a int)
returns bit
as
begin
return isnull(@a-@a+1,0)
end
so then you can use it whenever you need by simply calling
这样您就可以在需要时通过简单地调用来使用它
select dbo.isNotNull(myIntColumn) from myTable
The answer provided by Tomalakis more universal though as it would work with any data type
Tomalak提供的答案更通用，因为它适用于任何数据类型
回答by Patrick Parent
You may want to do a Convert(BIT, Value) of your result. Because something SQL will return an error that the value is not a boolean.
您可能想要对结果进行 Convert(BIT, Value) 转换。因为某些 SQL 会返回一个错误，即该值不是布尔值。
回答by Torbj?rn Gyllebring
isnull(column - column + 1, 0) != 0
回答by WW.
In Oracle, assuming you use 0 for false and 1 for true:-
在 Oracle 中，假设您使用 0 表示假，使用 1 表示真：-
SELECT DECODE( col, NULL, 0, 1) FROM ...
You can also write this using the CASE syntax, but the above is idiomatic in Oracle. DECODE is a bit like a switch/case; if col is NULL then 0 is returned, else 1.
您也可以使用 CASE 语法编写此代码，但以上内容在 Oracle 中是惯用的。DECODE 有点像开关/外壳；如果 col 为 NULL，则返回 0，否则返回 1。
回答by PL_kolek
The shortest one I know for Oracle:
我所知道的 Oracle 最短的一个：
SELECT NVL2(nullableColumn, 1, 0) FROM someTable
NVL2(value, ifNotNull, ifNull)returns ifNotNullif the valueis not null, and ifNullotherwise.
NVL2(value, ifNotNull, ifNull)ifNotNull如果value不为空则返回，ifNull否则返回。
回答by David Castro
Usually when use 1, it means is true and else in other case.
通常当使用 1 时，表示为真，否则为其他情况。
So:
所以：
SELECT IsDefault = CASE WHEN IsDefault = 1 THEN 'true' ELSE 'false' END
FROM table
回答by Daniel Hudsky
The syntax works, but I had to figure out how to place it in my query. If OK, I'd like to share an example on how to fit into an extended query:
语法有效，但我必须弄清楚如何将它放在我的查询中。如果可以，我想分享一个关于如何适应扩展查询的示例：
select count(*) as count, inventory,
CASE WHEN inventory = 0 THEN 'empty' ELSE 'not empty' END as InventoryStatus
from mytable group by count, inventory
