我要做的是对同一列使用多个CASE WHEN条件。

下面是我的查询代码:

   SELECT   Url='',
            p.ArtNo,
            p.[Description],
            p.Specification,
            CASE 
            WHEN 1 = 1 or 1 = 1 
               THEN 1 
               ELSE 0 
            END as Qty,
            p.NetPrice,
            [Status] = 0
      FROM  Product p (NOLOCK)

然而,我想做的是对同一列“qty”使用多个WHEN。

如下代码所示:

IF
// CODE
ELSE IF
// CODE
ELSE IF
// CODE
ELSE
// CODE

当前回答

case表达式有三种格式。你可以用很多WHEN as做CASE;

CASE  WHEN Col1 = 1 OR Col3 = 1  THEN 1 
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

或者一个简单的CASE表达式

CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END

或个案中个案作为;

CASE  WHEN Col1 < 2 THEN  
                    CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
      WHEN Col1 = 2 THEN 2
      ...
      ELSE 0 END as Qty

其他回答

当有多种情况时,你可以使用下面的例子。

SELECT
  id,stud_name,
  CASE
    WHEN marks <= 40 THEN 'Bad'
    WHEN (marks >= 40 AND
      marks <= 100) THEN 'good'
    ELSE 'best'
  END AS Grade
FROM Result

这是对一条语句执行不同测试的有效方法

select
case colour_txt 
  when 'red' then 5 
  when 'green' then 4 
  when 'orange' then 3
else 0 
end as Pass_Flag

这只适用于平等比较!

您将从这段代码中得到一个概念。

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

它只是你需要多个WHEN为一个单一的情况下表现得像if..Elseif其他. .

       CASE WHEN 1=1       --if
       THEN
        WHEN 1=1              --else if
         THEN ... 
        WHEN ...             --else if
        THEN 
        ELSE                      --else
       ......
         END

我有一个类似的,但它是关于日期的。 查询显示所有项目为上个月,工作伟大没有条件直到1月。 为了使其正确工作,需要添加一个年和月变量

declare @yr int
declare @mth int

set @yr=(select case when month(getdate())=1 then YEAR(getdate())-1 else YEAR(getdate())end)
set @mth=(select case when month(getdate())=1 then month(getdate())+11 else month(getdate())end)

现在我只需要将变量添加到condition中: ...

(year(CreationTime)=@yr and MONTH(creationtime)=@mth)