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

例如:

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

当前回答

有多种情况。

SELECT

(CASE
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1001' THEN 'DM'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1002' THEN 'GS'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1003' THEN 'MB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1004' THEN 'MP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1005' THEN 'PL'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1008' THEN 'DM-27'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1011' THEN 'PB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1012' THEN 'UT-2'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1013' THEN 'JGC'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1014' THEN 'SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1015' THEN 'IR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1016' THEN 'UT-3'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1017' THEN 'UT-4'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1019' THEN 'KR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1020' THEN 'SYB-SB'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1021' THEN 'GR'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1022' THEN 'SYB-KP'
WHEN RIGHT((LEFT(POSID,5)),4) LIKE '1026' THEN 'BNS'

  ELSE ''
END) AS OUTLET

FROM matrixcrm.Transact

其他回答

为了完整起见,我想补充一点,SQL使用三值逻辑。表达式:

obsolete = 'N' OR instock = 'Y'

可能产生三种不同的结果:

| obsolete | instock | saleable |
|----------|---------|----------|
| Y        | Y       | true     |
| Y        | N       | false    |
| Y        | null    | null     |
| N        | Y       | true     |
| N        | N       | true     |
| N        | null    | true     |
| null     | Y       | true     |
| null     | N       | null     |
| null     | null    | null     |

例如,如果一个产品已经过时,但你不知道产品是否库存,那么你不知道该产品是否可销售。您可以按如下方式编写此三值逻辑:

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           WHEN NOT (obsolete = 'N' OR instock = 'Y') THEN 'false'
           ELSE NULL
       END AS saleable

一旦确定了它的工作原理,就可以通过决定null的行为将三个结果转换为两个结果。例如,这会将null视为不可销售:

SELECT CASE
           WHEN obsolete = 'N' OR instock = 'Y' THEN 'true'
           ELSE 'false' -- either false or null
       END AS saleable

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

这不是答案,只是我工作时使用的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]

我喜欢使用CASE语句,但问题是在SQLSelect中使用IF语句。我过去使用的是:

SELECT

   if(GENDER = "M","Male","Female") as Gender

FROM ...

这就像Excel或sheets IF语句,其中有一个条件,后跟true条件,然后是false条件:

if(condition, true, false)

此外,您还可以嵌套if语句(但use应该使用CASE:-)

(注意:这在MySQL Workbench中有效,但在其他平台上可能不起作用)

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