如何在SQL SELECT语句中执行IF…THEN?
例如:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
如何在SQL SELECT语句中执行IF…THEN?
例如:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
当前回答
使用CASE语句:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
其他回答
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
SELECT
if((obsolete = 'N' OR instock = 'Y'), 1, 0) AS saleable, *
FROM
product;
通过此链接,我们可以了解T-SQL中的IF THEN ELSE:
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'
这对t-SQL不够好吗?
为了完整起见,我想补充一点,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
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product