在 SQL Server 中用常数 1 或 0 表示位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1172795/
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
Imply bit with constant 1 or 0 in SQL Server
提问by Damien McGivern
Is it possible to express 1 or 0 as a bit when used as a field value in a select statement?
在 select 语句中用作字段值时，是否可以将 1 或 0 表示为一个位？
e.g.
例如
In this case statement (which is part of a select statement) ICourseBased is of type int.
在这种情况下，语句（它是选择语句的一部分）ICourseBased 是 int 类型。
case
when FC.CourseId is not null then 1
else 0
end
as IsCoursedBased
To get it to be a bit type I have to cast both values.
为了让它有点类型，我必须转换两个值。
case
when FC.CourseId is not null then cast(1 as bit)
else cast(0 as bit)
end
as IsCoursedBased
Is there a short hand way of expressing the values as bit type without having to cast every time?
是否有一种简便的方法可以将值表示为位类型而不必每次都进行转换？
(I'm using MS SQL Server 2005)
（我使用的是 MS SQL Server 2005）
回答by gbn
cast (
case
when FC.CourseId is not null then 1 else 0
end
as bit)
The CAST spec is "CAST (expression AS type)". The CASE is an expressionin this context.
CAST 规范是“CAST（表达式 AS 类型）”。CASE 是这种情况下的一种表达方式。
If you have multiple such expressions, I'd declare bit vars @true and @false and use them. Or use UDFs if you really wanted...
如果您有多个这样的表达式，我会声明位变量 @true 和 @false 并使用它们。或者，如果您真的想要，请使用 UDF...
DECLARE @True bit, @False bit;
SELECT @True = 1, @False = 0; --can be combined with declare in SQL 2008
SELECT
case when FC.CourseId is not null then @True ELSE @False END AS ...
回答by Michael Petrotta
You might add the second snippet as a field definition for ICourseBased in a view.
您可以在视图中添加第二个片段作为 ICourseBased 的字段定义。
DECLARE VIEW MyView
AS
SELECT
case
when FC.CourseId is not null then cast(1 as bit)
else cast(0 as bit)
end
as IsCoursedBased
...
SELECT ICourseBased FROM MyView
回答by Gary McGill
No, but you could cast the whole expression rather than the sub-components of that expression. Actually, that probably makes it lessreadable in this case.
不，但您可以转换整个表达式而不是该表达式的子组件。实际上，在这种情况下，这可能会使它的可读性降低。
回答by kpkpkp
Slightly more condensed than gbn's:
比 gbn 更简洁：
Assuming CourseIdis non-zero
假设CourseId非零
CAST (COALESCE(FC.CourseId, 0) AS Bit)
COALESCEis like an ISNULL(), but returns the first non-Null.
COALESCE就像一个ISNULL()，但返回第一个非空值。
A Non-Zero CourseIdwill get type-cast to a 1, while a null CourseIdwill cause COALESCE to return the next value, 0
非零CourseId将被类型转换为 1，而 nullCourseId将导致 COALESCE 返回下一个值 0
回答by Fábio Nascimento
If you want the column is BIT and NOT NULL, you should put ISNULL before the CAST.
如果您希望该列是 BIT 且 NOT NULL，则应在 CAST 之前放置 ISNULL。
ISNULL(
CAST (
CASE
WHEN FC.CourseId IS NOT NULL THEN 1 ELSE 0
END
AS BIT)
,0) AS IsCoursedBased
回答by Andrew Hare
Unfortunately, no. You will have to cast each value individually.
抱歉不行。您必须单独转换每个值。
回答by IamProfChaos
The expression to use inside SELECT could be
在 SELECT 中使用的表达式可以是
CAST(IIF(FC.CourseId IS NOT NULL, 1, 0) AS BIT)
回答by Adrian S.
Enjoy this :) Without cast each value individually.
享受这个 :) 无需单独投射每个值。
SELECT ...,
IsCoursedBased = CAST(
CASE WHEN fc.CourseId is not null THEN 1 ELSE 0 END
AS BIT
)
FROM fc
