如何在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
当前回答
如果您是第一次将结果插入表中,而不是将结果从一个表传输到另一个表,这在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');
其他回答
SELECT
(CASE
WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
ELSE 'NO'
END) as Salable
, *
FROM Product
使用CASE语句:
SELECT CASE
WHEN (Obsolete = 'N' OR InStock = 'Y')
THEN 'Y'
ELSE 'N'
END as Available
etc...
使用纯位逻辑:
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的方法。
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
你可以在SQL CASE语句的威力中找到一些很好的例子,我认为你可以使用的语句应该是这样的(来自4guysfromrolla):
SELECT
FirstName, LastName,
Salary, DOB,
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Employees