where 子句中的 case 语句 - SQL Server [英] case statement in where clause - SQL Server
问题描述
我正在尝试在 SQL 查询的 where 子句中添加 case 或 if 语句.
我有一个包含开始和结束日期的旅程时间表,以及每天的布尔字段来表示当天旅程发生的地点.这是我到目前为止所拥有的,但我收到了不正确的语法错误:
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:
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
推荐答案
where
语句中不需要case
,只需使用括号和或代码>:
You don't need case
in the where
statement, just use parentheses and 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
)
此外,您的语法对于案例是错误的.它不会向字符串追加内容——它返回单个值.如果您真的要使用 case
语句(您不应该这样做),您会想要这样的东西:
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 case
statement (which you shouldn't):
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
另外,您可以使用 between
运算符作为您的日期:
And just for an extra umph, you can use the between
operator for your date:
where @Date between StartDate and EndDate
进行最终查询:
select
*
from
Times
where
@Date between StartDate and EndDate
and (
(@day = 'Monday' and Monday = 1)
or (@day = 'Tuesday' and Tuesday = 1)
or Wednesday = 1
)
这篇关于where 子句中的 case 语句 - SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!