case statement some what similar to if in SQL server

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

为了完整起见，我想补充一句，SQL使用三值逻辑。该表达式为：

obsolete = 'N' OR instock = 'Y'

可以产生三种截然不同的结果：

| obsolete | instock | saleable |
|----------|---------|----------|
| Y        | Y       | true     |
| Y        | N       | false    |
| Y        | null    | null     |
| N        | Y       | true     |
| N        | N       | true     |
| N        | null    | true     |
| null     | Y       | true     |
| null     | N       | null     |
| null     | null    | null     |

例如，如果一个产品过时了，但你不知道产品是否有库存，那么你就不知道产品是否畅销。您可以按如下方式编写此三值逻辑：

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           WHEN NOT (obsolete = 'N' OR instock = 'Y') THEN 'false'
           ELSE NULL
       END AS saleable

一旦弄清楚了它的工作原理，就可以通过决定NULL的行为将三个结果转换为两个结果。例如，这会将Null视为不可销售：

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           ELSE 'false' -- either false or null
       END AS saleable

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

我喜欢使用CASE语句，但问题要求在SQL Select中使用IF语句。我过去用过的是：

SELECT

   if(GENDER = "M","Male","Female") as Gender

FROM ...

它类似于Excel或Sheets IF语句，其中条件后跟TRUE条件，然后是FALSE条件：

if(condition, true, false)

此外，您可以嵌套if语句(但使用时应使用case：-)

(注：此功能适用于MySQL Workbench，但可能不适用于其他平台)

大概是这样的：

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN "The quantity is greater than 30"
    WHEN Quantity = 30 THEN "The quantity is 30"
    ELSE "The quantity is under 30"
END AS QuantityText
FROM OrderDetails;

Using SQL CASE is just like normal If / Else statements. In the below query, if obsolete value = 'N' or if InStock value = 'Y' then the output will be 1. Otherwise the output will be 0. Then we put that 0 or 1 value under the Salable Column.

SELECT
      CASE
           WHEN obsolete = 'N' OR InStock = 'Y'
          THEN 1
        ELSE 0
      END AS Salable
      , *
FROM PRODUCT

Question:

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

ANSI:

Select 
  case when p.Obsolete = 'N' 
  or p.InStock = 'Y' then 1 else 0 end as Saleable, 
  p.* 
FROM 
  Product p;

Using aliases -- p in this case -- will help prevent issues.

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

SELECT
if((obsolete = 'N' OR instock = 'Y'), 1, 0) AS saleable, *
FROM
product;

For those who uses SQL Server 2012, IIF is a feature that has been added and works as an alternative to Case statements.

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product

You can have two choices for this to actually implement:

1. Using IIF, which got introduced from SQL Server 2012:

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

1. Using Select Case:

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

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

作为CASE语句的替代解决方案，可以使用表驱动方法：

DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10))
INSERT INTO @Product VALUES
(1,'N','Y'),
(2,'A','B'),
(3,'N','B'),
(4,'A','Y')

SELECT P.* , ISNULL(Stmt.Saleable,0) Saleable
FROM
    @Product P
    LEFT JOIN
        ( VALUES
            ( 'N', 'Y', 1 )
        ) Stmt (Obsolete, InStock, Saleable)
        ON  P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete

结果：

ID          Obsolete   InStock    Saleable
----------- ---------- ---------- -----------
1           N          Y          1
2           A          B          0
3           N          B          1
4           A          Y          1

如果您是第一次将结果插入到表中，而不是将结果从一个表传输到另一个表，则这在Oracle 11.2g中有效：

INSERT INTO customers (last_name, first_name, city)
    SELECT 'Doe', 'John', 'Chicago' FROM dual
    WHERE NOT EXISTS 
        (SELECT '1' from customers 
            where last_name = 'Doe' 
            and first_name = 'John'
            and city = 'Chicago');

这不是一个答案，只是我工作的地方使用的案例语句的一个例子。它有一个嵌套的CASE语句。现在你知道为什么我的眼睛是交叉的了。

CASE orweb2.dbo.Inventory.RegulatingAgencyName
    WHEN 'Region 1'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 2'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'Region 3'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
    WHEN 'DEPT OF AGRICULTURE'
        THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
    ELSE (
            CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
                WHEN 1
                    THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
                ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
                END
            )
    END AS [County Contact Name]