如何根据列的内容在 T-SQL 中输出布尔值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/157114/
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
How to output a boolean in T-SQL based on the content of a column?
提问by Anheledir
I made a view to abstract columns of different tables and pre-filter and pre-sort them. There is one column whose content I don't care about but I need to know whether the content is null or not. So my view should pass an alias as "true" in case the value of this specified column isn't nulland "false" in case the value is null.
我做了一个视图来抽象不同表的列，并对它们进行预过滤和预排序。有一列我不关心其内容，但我需要知道内容是否为空。所以我的视图应该将别名传递为“ true”，以防此指定列的值不为 null和“ false”，以防值为null。
How can I select such a boolean with T-SQL?
如何使用 T-SQL 选择这样的布尔值？
回答by Adam Bellaire
You have to use a CASEstatement for this:
您必须为此使用CASE语句：
SELECT CASE WHEN columnName IS NULL THEN 'false' ELSE 'true' END FROM tableName;
回答by Schnapz
Or you can do like this:
或者你可以这样做：
SELECT RealColumn, CAST(0 AS bit) AS FakeBitColumn FROM tblTable
回答by Mahesh
If you need a output as boolean
如果您需要输出为布尔值
CAST(CASE WHEN colName IS NULL THEN 0 ELSE 1 END as BIT) aIsBooked
回答by tocsoft
for the column in the view you can use something like
对于视图中的列，您可以使用类似
CASE WHEN ColumnName is not null THEN 'True' ELSE 'False' END
or in a statement
或在声明中
SELECT
s.ID,
s.[Name],
CASE WHEN s.AchievedDate is not null THEN 'True' ELSE 'False' END [IsAchieved]
FROM Schools s
or for further processing afterwards I would personally use
或者为了以后的进一步处理，我个人会使用
SELECT
s.ID,
s.[Name],
CASE WHEN s.AchievedDate is not null THEN 1 ELSE 0 END [IsAchieved]
FROM Schools s
回答by lcrepas
I had a similar issue where I wanted a view to return a boolean column type based on if an actual column as null or not. I created a user defined function like so:
我有一个类似的问题，我希望视图根据实际列是否为空来返回布尔列类型。我创建了一个用户定义的函数，如下所示：
CREATE FUNCTION IsDatePopulated(@DateColumn as datetime)
RETURNS bit
AS
BEGIN
DECLARE @ReturnBit bit;
SELECT @ReturnBit =
CASE WHEN @DateColumn IS NULL
THEN 0
ELSE 1
END
RETURN @ReturnBit
END
Then the view that I created returns a bit column, instead of an integer.
然后我创建的视图返回一个位列，而不是一个整数。
CREATE VIEW testView
AS
SELECT dbo.IsDatePopulated(DateDeleted) as [IsDeleted]
FROM Company
回答by Steve Sether
You asked for boolean, which we call bit in t-sql.
您要求布尔值，我们在 t-sql 中将其称为位。
Other answers have either given you a varchar 'true' and 'false' or 1 and 0. 'true' and 'false' are obviously varchar, not boolean. I believe 1 and 0 would be cast as an integer, but it's certainly not a bit. This may seem nit-picky, but types matter quite often.
其他答案要么给了你一个 varchar 'true' 和 'false' 或 1 和 0。'true' 和 'false' 显然是 varchar，而不是布尔值。我相信 1 和 0 会被转换为整数，但肯定不是一点。这可能看起来很挑剔，但类型往往很重要。
To get an actual bit value, you need to cast your output explicitly as a bit like:
要获得实际的位值，您需要将输出显式转换为如下所示：
select case when tableName.columnName IS NULL then cast(0 as bit) else cast(1
as bit) END as ColumnLabel from tableName
