如何根据列的内容在T-SQL中输出一个布尔值?

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 null and "false" in case the value is null.

我查看了不同表的抽象列，并对它们进行预筛选和预排序。有一个列的内容我不关心，但我需要知道内容是否为null。因此，如果指定列的值不是null，那么我的视图应该传递一个别名为“true”，如果值为null，则传递一个别名为“false”。

Is this possible with T-SQL (Microsoft SQL Server 2000)?

是否可以使用T-SQL (Microsoft SQL Server 2000)?

Thanks in advance!

提前谢谢!

6 个解决方案

#1

You have to use a CASE statement for this:

你必须用一个案例来说明:

SELECT CASE WHEN columnName IS NULL THEN 'false' ELSE 'true' END FROM tableName;

#2

Or you can do like this:

或者你可以这样做:

    SELECT RealColumn, CAST(0 AS bit) AS FakeBitColumn FROM tblTable

#3

If you need a output as boolean

如果你需要一个布尔输出

CAST(CASE WHEN colName IS NULL THEN 0  ELSE 1   END as BIT) aIsBooked

#4

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

#5

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

#6

You asked for boolean, which we call bit in t-sql.

你要求布尔值，我们在t-sql中称之为bit。

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，而不是boolean。我相信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

选择案例时的表。columnName是空的，然后从tableName转换(0作为位)else转换(1作为位)，最后转换为ColumnLabel

#1