sql - 了解 "Where"子句中的 case 表达式

Question

我这里有这段代码，你可以从我的伪代码中看到我想要完成的事情

select *

from dbo.BenefitsForms
     inner join Dependents on BenefitsForms.UserId = Dependents.BenefitsForm_UserId
     inner join CoverageLevels on BenefitsForms.MedicalId = CoverageLevels.Id

where (BenefitsForms.MedicalId > 0 AND BenefitsForms.MedicalId < 13)
  AND Dependents.IsSpouse = CASE when CoverageLevels.[Level] = 2 then 1
                                 when CoverageLevels.[Level] = 3 then 0 end
                                 when CoverageLevels.[Level] = 4 then [any, it doesnt matter] <--- my desire but it doesn't work.

怎样才能得到括号里我想要的效果呢？如果 Coverage Level = 4 那么我不关心 Dependents.IsSpouse 是什么，我什至不需要再按它排序。

Answer 1

假设 isSpouse 只能是 0 或 1... 如果 CoverageLevels.Level 是 4，然后将 isSpouse 与自身进行比较，结果总是 true:

AND Dependents.IsSpouse = CASE 
    when CoverageLevels.[Level] = 2 then 1
    when CoverageLevels.[Level] = 3 then 0
    when CoverageLevels.[Level] = 4 then Dependents.IsSpouse
END

或者，这也可以在没有 CASE 的情况下表达:

WHERE
    BenefitsForms.MedicalId > 0 
    AND BenefitsForms.MedicalId < 13
    AND (
        (Dependents.IsSpouse = 1 AND CoverageLevels.[Level] = 2)
        OR (Dependents.IsSpouse = 0 AND CoverageLevels.[Level] = 3)
        OR CoverageLevels.[Level] = 4
    )