SQL Server-在WHERE子句中使用CASE

小编典典

SQL Server-在WHERE子句中使用CASE

sql

我想使用CASEinWHERE子句，但它返回错误。

我可以在where子句中使用CASE吗？或如何解决？谢谢

SELECT * FROM hris_leave.dbo.tbl_act 
inner join hris_leave.dbo.tstaff on hris_leave.dbo.tstaff.s_id =        hris_leave.dbo.tbl_act.s_id 
where 
 case when acting_to is not null 
then 
datediff(day, acting_from, acting_to) >= 90 and acting_to >= '2010-10-01' 
else
acting_to is null
order by  hris_leave.dbo.tbl_act.s_id

阅读 245

2021-04-14

共1个答案

小编典典

就您而言，您只需要OR

WHERE
    (
    acting_to is null 
    OR 
        (
        datediff(day, acting_from, acting_to) >= 90
        AND
        acting_to >= '2010-10-01'
        )
    )

一种情况是针对值，而不是条件。条件在CASE表达式之外

例如

CASE
    WHEN SomeCol = 'a' THEN ColA
    WHEN SomeCol = 'c' THEN ColC
    ELSE ColB
END > 42

2021-04-14