在 SQL Server 2008 中使用列别名指定数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3136050/
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
Specify data type with column alias in SQL Server 2008
提问by Brennan Vincent
Imagine I have the following SELECT statement in a view (SQL Server 2008):
想象一下，我在视图（SQL Server 2008）中有以下 SELECT 语句：
SELECT (SELECT CASE
WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y' THEN 1
ELSE 0
END) AS OOS
FROM HISTORY
The column OOS ends up being of type int, but I'd like it to be of type bit. How can I accomplish this?
OOS 列最终是 int 类型，但我希望它是 bit 类型。我怎样才能做到这一点？
回答by OMG Ponies
Use CAST/CONVERTto explicitly define the data type:
使用CAST/CONVERT显式定义数据类型：
SELECT CAST (CASE
WHEN LOWER(h.outofserv) = 'y' THEN 1
ELSE 0
END AS BIT) AS OOS
FROM HISTORY h
回答by Oleg
you should just use CAST(1 as bit)and CAST(0 as bit)instead of 1 and 0:
你应该只使用CAST(1 as bit)andCAST(0 as bit)而不是 1 和 0：
SELECT (CASE WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y'
THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END) AS OOS
FROM HISTORY
回答by Reagan Williams
Here's a way to get the desired BIT datatype output, and with a little cleaner code using:
这是一种获得所需 BIT 数据类型输出的方法，并使用更简洁的代码：
- upper()
cast()
SELECT CAST(CASE
WHEN UPPER(History.OutofServ) = 'Y' THEN 1
ELSE 0 END AS BIT) AS OOS FROM History
- 上（）
投掷（）
SELECT CAST(CASE
WHEN UPPER(History.OutofServ) = 'Y' THEN 1
ELSE 0 END AS BIT) AS OOS from History
I hope that helps.
我希望这有帮助。
