如何在SQLSELECT中执⾏IF…THEN？如何在SQL SELECT语句中执⾏IF...THEN ？
例如：
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
#1楼
如果您是第⼀次将结果插⼊表中，⽽不是将结果从⼀个表转移到另⼀个表，那么这在Oracle 11.2g中有效：
INSERT INTO customers (last_name, first_name, city)
SELECT 'Doe', 'John', 'Chicago' FROM dual
WHERE NOT EXISTS
教师演讲
(SELECT '1' from customers
where last_name = 'Doe'
and first_name = 'John'
and city = 'Chicago');
#2楼
懵懂读音使⽤纯位逻辑：
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
请参阅请参见。
⾸先，您需要计算选定条件下的true和fal值。 这是两个 ：
for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for fal: ISNULL(NULLIF(p.[Instock], 'N'), 0)
组合在⼀起得出1或0。接下来使⽤ 。
这是最⽅法。
#3楼
通过 ，我们可以了解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来说这还不够吗？
#4楼
对于使⽤SQL Server 2012的⽤户，IIF是⼀项已添加的功能，可以替代Ca语句。
SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM Product
#5楼
SELECT CASE fillno = 0 THEN 'N' ELSE 'R'END as newref
From profile
#6楼
ca statement some what similar to if in SQL rver
SELECT CASE
WHEN Obsolete = 'N' or InStock = 'Y'
THEN 1
ELSE 0
END as Saleable, *
FROM Product
#7楼
SQL Server中的简单if-el语句：
DECLARE @val INT;
SET @val = 15;
IF @val < 25
PRINT 'Hi Ravi Anand';
ELSE
PRINT 'By Ravi Anand.';
GO
SQL Server中的嵌套If ... el语句-
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
#8楼
这不是答案，只是我在⼯作中使⽤的CASE语句的⽰例。 它具有嵌套的CASE语句。 现在您知道为什么我的眼睛被交叉了。
CASE orweb2.dbo.Inventory.RegulatingAgencyName
WHEN 'Region 1'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 2'
解决近义词
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'Region 3'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactState
WHEN 'DEPT OF AGRICULTURE'
THEN orweb2.dbo.CountyStateAgContactInfo.ContactAg
ELSE (
CASE orweb2.dbo.CountyStateAgContactInfo.IsContract
WHEN 1
THEN orweb2.dbo.CountyStateAgContactInfo.ContactCounty
ELSE orweb2.dbo.CountyStateAgContactInfo.ContactState
END
)
END AS [County Contact Name]
#9楼
SQL Server 2012中添加了⼀个新功能 （我们可以简单地使⽤它）：
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
#10楼
SELECT 1 AS Saleable, *
FROM @Product
WHERE ( Obsolete = 'N' OR InStock = 'Y' )
UNION
SELECT 0 AS Saleable, *
FROM @Product
WHERE NOT ( Obsolete = 'N' OR InStock = 'Y' )
#11楼
SELECT IIF(Obsolete = 'N' OR InStock = 'Y',1,0) AS Saleable, * FROM Product
#12楼
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0
END AS Saleable, *
FROM Product
#13楼
招生简章范文
作为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
#14楼
SELECT
CAST(
CASE WHEN Obsolete = 'N'
or InStock = 'Y' THEN ELSE 0 END AS bit
) as Saleable, *
FROM
Product
#15楼
题：
SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
ANSI：
Select
ca when p.Obsolete = 'N'
or p.InStock = 'Y' then 1 el 0 end as Saleable,
p.*
FROM
Product p;
使⽤别名（在这种情况下为p将有助于防⽌出现问题。
#16楼
您可以通过两种选择来实际实现：
1. 使⽤从SQL Server 2012中引⼊的IIF：
SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
2. 使⽤Select Ca ：
SELECT CASE WHEN Obsolete = 'N' or InStock = 'Y' THEN 1 ELSE 0 END as Saleable, * FROM Product
#17楼
将会是这样的：
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;
