如何在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
当前回答
使用SQL CASE与普通的If/Else语句一样。在下面的查询中,如果过时值=“N”或InStock值=“Y”,则输出将为1。否则,输出将为0。然后我们将0或1值放在Salable列下。
SELECT
CASE
WHEN obsolete = 'N' OR InStock = 'Y'
THEN 1
ELSE 0
END AS Salable
, *
FROM PRODUCT
其他回答
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
Microsoft SQL Server(T-SQL)
在选择中,使用:
select case when Obsolete = 'N' or InStock = 'Y' then 'YES' else 'NO' end
在where子句中,使用:
where 1 = case when Obsolete = 'N' or InStock = 'Y' then 1 else 0 end
您可以有两种选择来实现:
使用从SQL Server 2012引入的IIF:选择IIF((过时=“N”或InStock=“Y”),1,0)作为可销售产品,*来自产品使用Select Case:选择大小写当过时=“N”或InStock=“Y”时然后1其他0结束销售*FROM产品
SQL Server 2012中添加了一个新功能IIF(我们可以简单使用):
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
如果您是第一次将结果插入表中,而不是将结果从一个表传输到另一个表,这在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');