如何在SQL SELECT中执行IF...THEN?

6mw9ycah  于 2022-09-18  发布在  Java
关注(0)|答案(30)|浏览(249)

如何在SQL SELECT语句中执行IF...THEN

例如:

SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product
34gzjxbg

34gzjxbg1#

CASE语句最接近于SQL中的if,并且所有版本的SQL Server都支持该语句。

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

如果希望结果为布尔值,则只需使用CAST运算符。如果您对int满意,则此方法有效:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE语句可以嵌入到其他CASE语句中,甚至可以包含在聚合中。

SQL Server Denali(SQL Server 2012)添加了IIF语句,该语句也可在access中找到(Martin Smith指出):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product
2ledvvac

2ledvvac2#

在这种情况下,案例陈述是您的朋友,它采用以下两种形式之一:

简单的情况是:

SELECT CASE <variable> WHEN <value>      THEN <returnvalue>
                       WHEN <othervalue> THEN <returnthis>
                                         ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>

扩展后的情况:

SELECT CASE WHEN <test>      THEN <returnvalue>
            WHEN <othertest> THEN <returnthis>
                             ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>

您甚至可以将CASE语句放在ORDER BY子句中,以实现真正的高级排序。

wgx48brx

wgx48brx3#

在SQL Server 2012中,您可以使用**IIF**function来执行此操作。

SELECT IIF(Obsolete = 'N' OR InStock = 'Y', 1, 0) AS Salable, *
FROM   Product

这实际上只是编写CASE的一种简写方式(尽管不是标准的SQL)。

与扩展的CASE版本相比,我更喜欢简洁。

IIF()CASE都可以解析为SQL语句中的表达式,并且只能在定义明确的位置使用。
CASE表达式不能用于控制Transact-SQL语句、语句块、用户定义函数和存储过程的执行流。

如果这些限制无法满足您的需求(例如,需要根据某些条件返回不同形式的结果集),则SQL Server也有一个过程性IF关键字。

IF @IncludeExtendedInformation = 1
  BEGIN
      SELECT A,B,C,X,Y,Z
      FROM   T
  END
ELSE
  BEGIN
      SELECT A,B,C
      FROM   T
  END

Care must sometimes be taken to avoid parameter sniffing issues with this approach however

cngwdvgl

cngwdvgl4#

您可以在*The Power of SQL CASE Statements*中找到一些很好的示例,我认为您可以使用的语句如下(来自4guysfromrolla):

SELECT
    FirstName, LastName,
    Salary, DOB,
    CASE Gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
    END
FROM Employees
qgelzfjb

qgelzfjb5#

用例。就像这样。

SELECT Salable =
        CASE Obsolete
        WHEN 'N' THEN 1
        ELSE 0
    END
w1jd8yoj

w1jd8yoj6#

SELECT  
(CASE 
     WHEN (Obsolete = 'N' OR InStock = 'Y') THEN 'YES'
                                            ELSE 'NO' 
 END) as Salable
, * 
FROM Product
mhd8tkvw

mhd8tkvw7#

Microsoft SQL Server(T-SQL)

select中,使用:

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
6yoyoihd

6yoyoihd8#

this link,我们可以理解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来说还不够好吗?

x8diyxa7

x8diyxa79#

SELECT
   CASE 
      WHEN OBSOLETE = 'N' or InStock = 'Y' THEN 'TRUE' 
      ELSE 'FALSE' 
   END AS Salable,
   * 
FROM PRODUCT
yc0p9oo0

yc0p9oo010#

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
jucafojl

jucafojl11#

使用CASE语句:

SELECT CASE
       WHEN (Obsolete = 'N' OR InStock = 'Y')
       THEN 'Y'
       ELSE 'N'
END as Available

etc...
zysjyyx4

zysjyyx412#

在SQL Server 2012中添加了一项新功能IIF(我们可以简单使用):

SELECT IIF ( (Obsolete = 'N' OR InStock = 'Y'), 1, 0) AS Saleable, * FROM Product
yv5phkfx

yv5phkfx13#

使用纯位逻辑:

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中没有case

对于Start,您需要计算出选定条件下的truefalse的值。下面是两个NULLIF

for true: ISNULL(NULLIF(p.[Instock], 'Y'), 1)
for false: ISNULL(NULLIF(p.[Instock], 'N'), 0)

加在一起会得到1或0。接下来使用bitwise operators

这是WYSIWYG最多的方法。

7bsow1i6

7bsow1i614#

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' )
z31licg0

z31licg015#

SELECT CASE WHEN profile.nrefillno = 0 THEN 'N' ELSE 'R'END as newref
From profile

相关问题