sql - 了解“Where”子句中的案例表达

我在这里有此代码，您可以从我的伪代码中看到我要完成的工作

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，那么我不在乎受抚养人。IsSpouse是，我什至不需要按其排序。

最佳答案

假定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
    )

关于sql - 了解“Where”子句中的案例表达，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13483001/