我要做的是对同一列使用多个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 first_condition
then first_condition_result_true
else
case when second_condition
then second_condition_result_true
else
second_condition_result_false
end
end
end as qty
其他回答
就像这样，两个条件两列
SELECT ITEMSREQ.ITEM AS ITEM,
ITEMSREQ.CANTIDAD AS CANTIDAD,
(CASE WHEN ITEMSREQ.ITEMAPROBADO=1 THEN 'APROBADO'
WHEN ITEMSREQ.ITEMAPROBADO=0 THEN 'NO APROBADO'
END) AS ITEMS,
(CASE
WHEN ITEMSREQ.ITEMAPROBADO = 0
THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM NO APROBADO PARA ENTREGA' END
WHEN ITEMSREQ.ITEMAPROBADO = 1
THEN CASE WHEN REQUISICIONES.RECIBIDA IS NULL THEN 'ITEM AUN NO RECIBIDO'
WHEN REQUISICIONES.RECIBIDA=1 THEN 'RECIBIDO'
WHEN REQUISICIONES.RECIBIDA=0 THEN 'NO RECIBIDO'
END
END)
AS RECIBIDA
FROM ITEMSREQ
INNER JOIN REQUISICIONES ON
ITEMSREQ.CNSREQ = REQUISICIONES.CNSREQ
case when first_condition
then first_condition_result_true
else
case when second_condition
then second_condition_result_true
else
second_condition_result_false
end
end
end as qty
只要用这个，当他们是类的时候，你必须用更多。
SELECT Url='',
p.ArtNo,
p.[Description],
p.Specification,
CASE
WHEN 1 = 1 or 1 = 1
THEN 1
WHEN 2 = 2
THEN 2
WHEN 3 = 3
THEN 3
ELSE 0
END as Qty,
p.NetPrice,
[Status] = 0
FROM Product p (NOLOCK)
case
when a.REASONID in ('02','03','04','05','06') then
case b.CALSOC
when '1' then 'yes'
when '2' then 'no'
else 'no'
end
else 'no'
end
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
