本文共 8292 字,大约阅读时间需要 27 分钟。
如何在SQL SELECT
语句中执行IF...THEN
?
例如:
SELECT IF(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');
使用纯位逻辑:
DECLARE @Product TABLE ( id INT PRIMARY KEY IDENTITY NOT NULL ,Obsolote CHAR(1) ,Instock CHAR(1))INSERT INTO @Product ([Obsolote], [Instock]) VALUES ('N', 'N'), ('N', 'Y'), ('Y', 'Y'), ('Y', 'N');WITH cteAS( SELECT 'CheckIfInstock' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Instock], 'Y'), 1), 'N'), 0) AS BIT) ,'CheckIfObsolote' = CAST(ISNULL(NULLIF(ISNULL(NULLIF(p.[Obsolote], 'N'), 0), 'Y'), 1) AS BIT) ,* FROM @Product AS p)SELECT 'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote] ,*FROM [cte] c
请参阅请参见。
首先,您需要计算选定条件下的true
和false
值。 这是两个 :
for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)
组合在一起得出1或0。接下来使用 。
这是最方法。
通过 ,我们可以了解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 Server 2012的用户,IIF是一项已添加的功能,可以替代Case语句。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *FROM Product
SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newrefFrom profile
case statement some what similar to if in SQL serverSELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Saleable, * FROM Product
SQL Server中的简单if-else语句:
DECLARE @val INT;SET @val = 15;IF @val < 25PRINT 'Hi Ravi Anand';ELSEPRINT 'By Ravi Anand.';GO
SQL Server中的嵌套If ... else语句-
DECLARE @val INT;SET @val = 15;IF @val < 25PRINT 'Hi Ravi Anand.';ELSEBEGINIF @val < 50 PRINT 'what''s up?';ELSE PRINT 'Bye Ravi Anand.';END;GO
这不是答案,只是我在工作中使用的CASE语句的示例。 它具有嵌套的CASE语句。 现在您知道为什么我的眼睛被交叉了。
CASE orweb2.dbo.Inventory.RegulatingAgencyName WHEN 'Region 1' THEN orweb2.dbo.CountyStateAgContactInfo.ContactState WHEN 'Region 2' THEN orweb2.dbo.CountyStateAgContactInfo.ContactState WHEN 'Region 3' THEN orweb2.dbo.CountyStateAgContactInfo.ContactState WHEN 'DEPT OF AGRICULTURE' THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg ELSE ( CASE orweb2.dbo.CountyStateAgContactInfo.IsContract WHEN 1 THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState END ) END AS [County Contact Name]
SQL Server 2012中添加了一个新功能 (我们可以简单地使用它):
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
SELECT 1 AS Saleable, * FROM @Product WHERE ( Obsolete = 'N' OR InStock = 'Y' )UNIONSELECT 0 AS Saleable, * FROM @Product WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
SELECT IIF(Obsolete = 'N' OR InStock = 'Y',1,0) AS Saleable, * FROM Product
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END AS Saleable, * FROM Product
作为CASE
语句的替代解决方案,可以使用表驱动的方法:
DECLARE @Product TABLE (ID INT, Obsolete VARCHAR(10), InStock VARCHAR(10))INSERT INTO @Product VALUES(1,'N','Y'),(2,'A','B'),(3,'N','B'),(4,'A','Y')SELECT P.* , ISNULL(Stmt.Saleable,0) SaleableFROM @Product P LEFT JOIN ( VALUES ( 'N', 'Y', 1 ) ) Stmt (Obsolete, InStock, Saleable) ON P.InStock = Stmt.InStock OR P.Obsolete = Stmt.Obsolete
结果:
ID Obsolete InStock Saleable----------- ---------- ---------- -----------1 N Y 12 A B 03 N B 14 A Y 1
SELECT CAST( CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN ELSE 0 END AS bit ) as Saleable, * FROM Product
题:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
ANSI:
Select case when p.Obsolete = 'N' or p.InStock = 'Y' then 1 else 0 end as Saleable, p.* FROM Product p;
使用别名(在这种情况下为p
将有助于防止出现问题。
您可以通过两种选择来实际实现:
使用从SQL Server 2012中引入的IIF:
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
使用Select Case
:
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Saleable, * FROM Product
将会是这样的:
SELECT OrderID, Quantity,CASE WHEN Quantity > 30 THEN "The quantity is greater than 30" WHEN Quantity = 30 THEN "The quantity is 30" ELSE "The quantity is under 30"END AS QuantityTextFROM OrderDetails;
为了完整起见,我要补充一点,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
使用案例。 这样的事情。
SELECT Salable = CASE Obsolete WHEN 'N' THEN 1 ELSE 0 END
SELECT CASE WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' ELSE 'FALSE' 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)添加了语句,该语句也可以在中 (由指出):
SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
您可以在找到一些不错的示例,我认为您可以使用的语句将类似于以下内容(来自 ):
SELECT FirstName, LastName, Salary, DOB, CASE Gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ENDFROM Employees
Microsoft SQL Server(T-SQL)
在select
,使用:
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
使用CASE语句:
SELECT CASE WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'Y' ELSE 'N'END as Availableetc...
在这种情况下,案例陈述是您的朋友,采用两种形式之一:
简单的情况:
SELECT CASEWHEN THEN WHEN THEN ELSE END AS FROM
扩展案例:
SELECT CASE WHENTHEN WHEN THEN ELSE END AS FROM
您甚至可以将case语句放在order by子句中,以进行真正的排序。
在SQL Server 2012中,您可以使用 。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *FROM Product
实际上,这只是编写CASE
一种简捷方式(尽管不是标准的SQL)。
与扩展的CASE
版本相比,我更简洁。
IIF()
和CASE
都可解析为SQL语句中的表达式,并且只能在定义明确的位置使用。
CASE表达式不能用于控制Transact-SQL语句,语句块,用户定义的函数和存储过程的执行流程。
如果这些限制不能满足您的需求(例如,需要根据某些条件返回形状不同的结果集),则SQL Server也会提供过程关键字。
IF @IncludeExtendedInformation = 1 BEGIN SELECT A,B,C,X,Y,Z FROM T ENDELSE BEGIN SELECT A,B,C FROM T END
。
转载地址:http://qbdnb.baihongyu.com/