where 子句中的 case 语句 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8730933/
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
case statement in where clause - SQL Server
提问by e-on
I'm trying to add a case or if statement in the where clause of my SQL query.
I have a table of journey times with a start and end date, and a boolean field for each day to signify where the journey happens on that day. Here is what I have so far, but I'm getting incorrect syntax errors:
我正在尝试在 SQL 查询的 where 子句中添加 case 或 if 语句。
我有一个包含开始和结束日期的旅程时间表，以及每天的布尔字段来表示当天旅程发生的地点。这是我到目前为止所拥有的，但我收到了不正确的语法错误：
declare @date datetime
set @Date = '05/04/2012'
declare @day nvarchar(50)
set @day = 'Monday'
Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date)
CASE WHEN @day = 'Monday' THEN
AND (Monday = 1)
WHEN @day = 'Tuesday' THEN
AND (Tuesday = 1)
ELSE
AND (Wednesday = 1)
END
回答by Eric
You don't need casein the wherestatement, just use parentheses and or:
您不需要case在where语句中，只需使用括号和or：
Select * From Times
WHERE StartDate <= @Date AND EndDate >= @Date
AND (
(@day = 'Monday' AND Monday = 1)
OR (@day = 'Tuesday' AND Tuesday = 1)
OR Wednesday = 1
)
Additionally, your syntax is wrong for a case. It doesn't append things to the string--it returns a single value. You'd want something like this, if you were actually going to use a casestatement (which you shouldn't):
此外，对于一个案例，您的语法是错误的。它不会向字符串追加内容——它返回单个值。如果你真的要使用一个case语句（你不应该这样做），你会想要这样的东西：
Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date)
AND 1 = CASE WHEN @day = 'Monday' THEN Monday
WHEN @day = 'Tuesday' THEN Tuesday
ELSE Wednesday
END
And just for an extra umph, you can use the betweenoperator for your date:
只是为了额外的 umph，您可以使用between运算符作为您的约会对象：
where @Date between StartDate and EndDate
Making your final query:
进行您的最终查询：
select
*
from
Times
where
@Date between StartDate and EndDate
and (
(@day = 'Monday' and Monday = 1)
or (@day = 'Tuesday' and Tuesday = 1)
or Wednesday = 1
)
回答by aF.
simply do the select:
只需执行以下操作 select：
Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date) AND
((@day = 'Monday' AND (Monday = 1))
OR (@day = 'Tuesday' AND (Tuesday = 1))
OR (Wednesday = 1))
回答by jklemmack
A CASEstatement is an expression, just like a boolean comparison. That means the 'AND' needs to go before the 'CASE' statement, not within it.:
一个CASE语句是一个表情，就像一个布尔比较。这意味着“AND”需要在“CASE”语句之前，而不是在其中。：
Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date)
AND -- Added the "AND" here
CASE WHEN @day = 'Monday' THEN (Monday = 1) -- Removed "AND"
WHEN @day = 'Tuesday' THEN (Tuesday = 1) -- Removed "AND"
ELSE AND (Wednesday = 1)
END
