where子句中的case语句-SQL Server

小编典典

where子句中的case语句-SQL Server

sql

我试图在我的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

阅读 163

2021-03-17

共1个答案

小编典典

您不需要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
)

此外，您的语法在某种情况下是错误的。它不会将内容附加到字符串中，而是返回一个值。如果您实际上要使用一条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

只需多花些钱，您就可以将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
    )

2021-03-17