where语句中的case语句 - SQL Server
我想在我的SQL查询的where子句中添加一个case或if语句。
我有一张开始和结束日期的旅程时间表,以及每天的布尔型字段来表示旅程在当天发生的地点。这里是我到目前为止,但我发现了不正确的语法错误:where语句中的case语句 - SQL Server
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
,只需用括号和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
语句(你不应该):
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
而只是一个额外的umph,你可以使用between
运营商为您的日期:
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
)
谢谢埃里克 - 这是完美的:) – 2012-01-05 09:37:26
@Eric这会减慢查询 – 2013-09-30 11:37:34
简单,做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))
对我来说,这将是愚蠢的downvote你,但你可以在'where'子句中使用'case'。你不能像OP所要做的那样使用它。 – Eric 2012-01-04 17:20:21
在Microsoft SQL Server中,确实可以在'WHERE'子句中使用'CASE'。试试这个 - 解析并执行得很好(在SQL 2008 R2上):“select * from sys.tables t where case when t.schema_id = 1 then 1 else 0 end = 1” – jklemmack 2012-01-04 17:22:17
@Eric谢谢:)然后修复: P – 2012-01-04 17:22:41
A CASE
语句是一个表达式,就像一个布尔比较。这意味着“与”需要“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
Thx用于编辑Dylan。今天早上我一定是在引导VB。 – jklemmack 2012-01-04 18:15:39
嗨jklemmack,我最初尝试过,并得到了一个“不正确的语法附近'='”的错误在案件陈述的第一行。当我执行这个查询时仍然会这样 – 2012-01-05 09:42:21
在我看来,如果你有表的一个设计问题,有没有办法,你应该永远有列星期一,星期二,星期三等。您应该记住daya专栏,并填写您想要的日期或数字值,以查找您想要的日期。如果超过一天适用,则使用相关表格。 – HLGEM 2012-01-04 17:17:49