如何在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 Server 2012的用户来说,IIF是一个已添加的功能,可以作为Case语句的替代。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
其他回答
使用CASE语句:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
SQL Server中的简单if-else语句:
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
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
您可以有两种选择来实现:
使用从SQL Server 2012引入的IIF:选择IIF((过时=“N”或InStock=“Y”),1,0)作为可销售产品,*来自产品使用Select Case:选择大小写当过时=“N”或InStock=“Y”时然后1其他0结束销售*FROM产品
问题:
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
答案:
Select
case when p.Obsolete = 'N'
or p.InStock = 'Y' then 1 else 0 end as Saleable,
p.*
FROM
Product p;
使用别名(在本例中为p)将有助于防止出现问题。
通过此链接,我们可以了解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不够好吗?