在SQL Server 2008中使用列别名指定数据类型

Question

假设我在视图中有以下SELECT语句（SQL Server 2008）：

SELECT (SELECT CASE 
                 WHEN HISTORY.OUTOFSERV = 'Y' OR HISTORY.OUTOFSERV = 'y' THEN 1 
                 ELSE 0 
               END) AS OOS 
  FROM HISTORY

еҲ—OOSжңҖз»ҲжҲҗдёәintзұ»еһӢпјҢдҪҶжҲ‘еёҢжңӣе®ғжҳҜbitзұ»еһӢгҖӮжҲ‘иҜҘеҰӮдҪ•е®һзҺ°пјҹ

- Brennan Vincent

Answer 1

使用CAST/CONVERT来显式定义数据类型：

SELECT CAST (CASE 
               WHEN LOWER(h.outofserv) = 'y' THEN 1 
               ELSE 0 
             END AS BIT) AS OOS 
 FROM HISTORY h

- OMG Ponies

Answer 2

在IT技术方面，建议您使用CAST(1 as bit)和CAST(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

- Oleg

Answer 3

这里有一种方法可以获得所需的BIT数据类型输出，并使用更简洁的代码：

• upper()

• cast()

  SELECT CAST(CASE
  WHEN UPPER(History.OutofServ) = 'Y' THEN 1
  ELSE 0 END AS BIT) AS OOS FROM History

希望这能帮到您。

- Reagan Williams