如何使用 SQL Server 2008 执行多个 CASE WHEN 条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14630984/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I do multiple CASE WHEN conditions using SQL Server 2008?
提问by Nils Anders
What I'm trying to do is use more than one CASE WHEN condition for the same column.
我想要做的是对同一列使用多个 CASE WHEN 条件。
Here is my code for the query:
这是我的查询代码：
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)
However, what I want to do is use more then one WHEN for the same column "qty".
但是，我想要做的是对同一列“数量”使用多个 WHEN。
As in the following code:
如以下代码所示：
IF
// CODE
ELSE IF
// CODE
ELSE IF
// CODE
ELSE
// CODE
回答by Kaf
There are two formats of case expression. You can do CASEwith many WHENas;
CASE WHEN Col1 = 1 OR Col3 = 1 THEN 1
WHEN Col1 = 2 THEN 2
...
ELSE 0 END as Qty
Ora Simple CASEexpression
或者一个简单的CASE表达
CASE Col1 WHEN 1 THEN 11 WHEN 2 THEN 21 ELSE 13 END
OrCASEwithinCASEas;
或CASE内CASE为；
CASE WHEN Col1 < 2 THEN
CASE Col2 WHEN 'X' THEN 10 ELSE 11 END
WHEN Col1 = 2 THEN 2
...
ELSE 0 END as Qty
回答by Shankar
Just use this one, You have to use more when they are classes.
就用这个，当它们是类时你必须使用更多。
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)
回答by Abhijeet Navgire
You can use below example of case when with multiple conditions.
当有多个条件时，您可以使用以下案例示例。
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
回答by user2082785
This can be an efficient way of performing different tests on a single statement
这可以是对单个语句执行不同测试的有效方法
select
case colour_txt
when 'red' then 5
when 'green' then 4
when 'orange' then 3
else 0
end as Pass_Flag
this only works on equality comparisons!
这仅适用于相等比较！
回答by wennykikkok
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
回答by Jimoc
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
回答by Douglas Bentley
I had a similar but it was dealing with dates. Query to show all items for the last month, works great without conditions until Jan. In order for it work correctly, needed to add a year and month variable
我有一个类似的，但它正在处理日期。查询以显示上个月的所有项目，在 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)
Now I just add the variable into condition: ...
现在我只是将变量添加到条件中：...
(year(CreationTime)=@yr and MONTH(creationtime)=@mth)
回答by Arun Prasad E S
Combining all conditions
结合所有条件
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 )
)
回答by Ricardo Roa
Something Like this, Two Conditions Two Columns
像这样的东西，两个条件两列
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
回答by Himanshu Ahuja
Its just that you need multiple Whenfor a single case to behave it like if.. Elseif else..
只是你需要多个When单个案例才能表现得像if.. Elseif else..
Case when 1=1 //if
Then
When 1=1 //else if
Then....
When ..... //else if
Then
Else //else
.......
End
