我要做的是对同一列使用多个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
当前回答
综合所有条件
select a.* from tbl_Company a
where a.Company_ID NOT IN (1,2)
AND (
(0 =
CASE WHEN (@Fromdate = '' or @Todate='')
THEN 0
ELSE 1
END
) -- if 0=0 true , if 0=1 fails (filter only when the fromdate and todate is present)
OR
(a.Created_Date between @Fromdate and @Todate )
)
其他回答
只要用这个，当他们是类的时候，你必须用更多。
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)
就像这样，两个条件两列
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
它只是你需要多个WHEN为一个单一的情况下表现得像if..Elseif其他. .
CASE WHEN 1=1 --if
THEN
WHEN 1=1 --else if
THEN ...
WHEN ... --else if
THEN
ELSE --else
......
END
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 a.* from tbl_Company a
where a.Company_ID NOT IN (1,2)
AND (
(0 =
CASE WHEN (@Fromdate = '' or @Todate='')
THEN 0
ELSE 1
END
) -- if 0=0 true , if 0=1 fails (filter only when the fromdate and todate is present)
OR
(a.Created_Date between @Fromdate and @Todate )
)
