How do I perform an IF...THEN in an SQL SELECT?
How do I perform an IF...THEN in an SQL SELECT statement?
For example:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
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
You only need to do the CAST 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 statements can be embedded in other CASE statements and even included in aggregates.
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
The case statement is your friend in this situation, and takes one of two forms:
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.
From SQL Server 2012 you can use the IIF function for this.
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 .
I prefer the conciseness when compared with the expanded CASE version.
Both IIF() and CASE resolve as expressions within a SQL Statement and can only be used in well defined places.
The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.
If your needs can not be satisfied by these limitations (for example a need to return differently shaped result sets dependant on some condition) then SQL Server does also have a procedural IF Keyword.
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.
链接地址: http://www.djcxy.com/p/2754.html