如何在 SQL SELECT 中执行 IF...THEN？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/63447/
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
How do I perform an IF...THEN in an SQL SELECT?
提问by Eric Labashosky
How do I perform an IF...THENin an SQL SELECTstatement?
如何IF...THEN在SQL SELECT语句中执行 an ？
For example:
例如：
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
回答by Darrel Miller
The CASEstatement is the closest to IF in SQL and is supported on all versions of SQL Server.
该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
You only need to do the CASTif you want the result as a Boolean value. If you are happy with an int, this works:
CAST如果您希望结果为布尔值，则只需执行。如果您对 a 感到满意int，则此方法有效：
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
CASEstatements can be embedded in other CASEstatements and even included in aggregates.
CASE语句可以嵌入到其他CASE语句中，甚至可以包含在聚合中。
SQL Server Denali (SQL Server 2012) adds the IIFstatement which is also available in access(pointed out by Martin Smith):
SQL Server Denali (SQL Server 2012) 添加了IIF语句，该语句也可用于访问（由Martin Smith指出）：
SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
回答by Jonathan
The case statement is your friend in this situation, and takes one of two forms:
在这种情况下，case 语句是您的朋友，它采用以下两种形式之一：
The simple case:
简单的情况：
SELECT CASE <variable> WHEN <value> THEN <returnvalue>
WHEN <othervalue> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
The extended case:
扩展案例：
SELECT CASE WHEN <test> THEN <returnvalue>
WHEN <othertest> THEN <returnthis>
ELSE <returndefaultcase>
END AS <newcolumnname>
FROM <table>
You can even put case statements in an order by clause for really fancy ordering.
您甚至可以将 case 语句放在 order by 子句中，以实现真正花哨的排序。
回答by Martin Smith
From SQL Server 2012 you can use the IIFfunctionfor this.
从 SQL Server 2012 开始，您可以为此使用该IIF函数。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
This is effectively just a shorthand (albeit not standard SQL) way of writing CASE.
这实际上只是一种速记（尽管不是标准 SQL）的书写方式CASE。
I prefer the conciseness when compared with the expanded CASEversion.
与扩展CASE版本相比，我更喜欢简洁。
Both IIF()and CASEresolve as expressions within a SQL statement and can only be used in well-defined places.
双方IIF()并CASE决心为SQL语句中的表达式，只能在明确规定的地方使用。
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
CASE 表达式不能用于控制 Transact-SQL 语句、语句块、用户定义函数和存储过程的执行流程。
If your needs can not be satisfied by these limitations (for example, a need to return differently shaped result sets dependent on some condition) then SQL Server does also have a procedural IFkeyword.
如果这些限制不能满足您的需求（例如，需要根据某些条件返回不同形状的结果集），那么 SQL Server 也有一个过程IF关键字。
IF @IncludeExtendedInformation = 1
BEGIN
SELECT A,B,C,X,Y,Z
FROM T
END
ELSE
BEGIN
SELECT A,B,C
FROM T
END
Care must sometimes be taken to avoid parameter sniffing issues with this approach however.
回答by sven
You can find some nice examples in The Power of SQL CASE Statements, and I think the statement that you can use will be something like this (from 4guysfromrolla):
你可以在The Power of SQL CASE Statements 中找到一些很好的例子，我认为你可以使用的语句是这样的（来自4guysfromrolla）：
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees
回答by palehorse
Use CASE. Something like this.
用例。像这样的东西。
SELECT Salable =
CASE Obsolete
WHEN 'N' THEN 1
ELSE 0
END
回答by John Sheehan
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
回答by user7658
Microsoft SQL Server (T-SQL)
Microsoft SQL Server (T-SQL)
In a select, use:
在 a 中select，使用：
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
In a whereclause, use:
在where子句中，使用：
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
回答by Ken
From this link, we can understand IF THEN ELSEin T-SQL:
从这个链接，我们可以IF THEN ELSE在 T-SQL 中理解：
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'ALFKI')
PRINT 'Need to update Customer Record ALFKI'
ELSE
PRINT 'Need to add Customer Record ALFKI'
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'LARSE')
PRINT 'Need to update Customer Record LARSE'
ELSE
PRINT 'Need to add Customer Record LARSE'
Isn't this good enough for T-SQL?
这对 T-SQL 来说还不够好吗？
回答by Santiago Cepas
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
FROM PRODUCT
回答by Ravi Anand
Simple if-else statement in SQL Server:
SQL Server 中的简单 if-else 语句：
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
Nested If...else statement in SQL Server -
SQL Server 中的嵌套 If...else 语句 -
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand.';
ELSE
BEGIN
IF @val < 50
PRINT 'what''s up?';
ELSE
PRINT 'Bye Ravi Anand.';
END;
GO
