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

Question

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

例如：

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

原文由 Eric Labashosky 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

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

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

如果您希望结果为布尔值，您只需使用 CAST 运算符。如果您对 int 感到满意，则可以使用：

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

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

SQL Server Denali (SQL Server 2012) 添加了 IIF 语句，该语句也可用于 访问（ Martin Smith 指出）：

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

原文由 Darrel Miller 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

有多种条件。

 SELECT

(CASE
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS'

  ELSE ''
END) AS OUTLET

FROM matrixcrm.Transact

原文由 durlove roy 发布，翻译遵循 CC BY-SA 4.0 许可协议