如何在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 statement some what similar to if in SQL server
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
其他回答
问题:
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)将有助于防止出现问题。
对于使用SQL Server 2012的用户来说,IIF是一个已添加的功能,可以作为Case语句的替代。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
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
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
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) Saleable
FROM
@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 1
2 A B 0
3 N B 1
4 A Y 1