如何在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:选择IIF((过时=“N”或InStock=“Y”),1,0)作为可销售产品,*来自产品使用Select Case:选择大小写当过时=“N”或InStock=“Y”时然后1其他0结束销售*FROM产品
其他回答
作为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
会是这样的:
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 QuantityText
FROM OrderDetails;
SELECT
CASE
WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE'
ELSE 'FALSE'
END AS Salable,
*
FROM PRODUCT
SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile
SQL Server 2012中添加了一个新功能IIF(我们可以简单使用):
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product