sqlrver的判断语句
⽅案⼀、使⽤ CASE 关键字
s开头的英文单词SELECT CAST(
CASE
WHEN Obsolete = 'N'or InStock = 'Y'
THEN1
ELSE0
END AS bit) as Saleable, *
FROM Product
以上语句返回的是⼀个布尔值，如何需要返回 int 类型呢，如下：
toutSELECT CASE
mgp
WHEN Obsolete = 'N'or InStock = 'Y'
THEN1
ELSE0
END as Saleable, *
FROM Product
⽅案⼆、使⽤ IIF 关键字
由于IIF 关键字是SQL Server 2012及上以版本才有的，所以本⽅案仅适合SQL Server 2012及以上版本，代码如下：SELECT IIF(Obsolete = 'N'or InStock = 'Y', 1, 0) as Saleable, * FROM Product
⽅案三
SELECT
(CASE
oilily
WHEN (Obsolete = 'N'OR InStock = 'Y') THEN'YES'
ELSE'NO'
END) as Salable
, *
FROM Product
⽅案四
IF EXISTS(SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerId = 'ALFKI')
PRINT 'Need to update Customer Record ALFKI'
the scarlet letter
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'
⽅案六
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
zub'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
)disappeared
float是什么意思
SELECT
'Salable' = c.[CheckIfInstock] & ~c.[CheckIfObsolote]
,*
FROM
[cte] c
⽅案七
SELECT1AS Saleable, *
FROM @Product
WHERE ( Obsolete = 'N'OR InStock = 'Y' )
UNION
SELECT0AS Saleable, *
FROM @Product
WHERE NOT ( Obsolete = 'N'OR InStock = 'Y' )
