如何在 SQL SELECT 中执行 IF...THEN?

2021-12-01 00:00:00 sql if-statement tsql sql-server case

如何在 SQL SELECT 语句中执行 IF...THEN?

How do I perform an IF...THEN in an SQL SELECT statement?

例如:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

推荐答案

CASE 语句是 SQL 中最接近 IF 的语句，所有版本的 SQL Server 都支持.

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

如果您希望结果为布尔值，则只需使用 CAST 运算符.如果您对 int 感到满意，则此方法有效:

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE 语句可以嵌入到其他 CASE 语句中，甚至可以包含在聚合中.

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) 添加了 IIF 声明，也可在 access (马丁·史密斯指出):

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

相关文章