如何在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
当前回答
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(我们可以简单使用):
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
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
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
使用纯位逻辑:
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 cte
AS
(
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
请参阅SQL Server中的工作演示:if then without case。
首先,需要计算所选条件的true和false值。下面是两个NULLIF:
for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)
组合在一起得到1或0。接下来使用按位运算符。
这是最WYSIWYG的方法。