sql - 如何在 WHERE 子句中编写条件？

Question

请求分为 2 部分:

1. 记录应从报告中脱落 _*如果记录已过期且if *_RecordStatus != '已资助'。

2. 记录应保留在报告中_如果 RecordStatus = 'Funded'*，没有*PURCHASEDATE，并且如果记录已过期(是从**当前日期过去)。

我尝试使用 CASE 执行此操作，但此请求似乎是 bool 逻辑，因此不切实际。我试图研究 IF...THEN...ELSE 但我不知道如何去做。我花了整整两天的时间试图让它发挥作用，但我一无所获。有没有人可以帮助我解决这个问题，然后解释一下？

下面是一个简单的失败示例(是的，我完全理解这是由于条件而不是表达式而失败):

Select distinct ...  
from ... --with joins  
where ...  
and case when RecordStatus != 'Funded' then getdate() <= EXPIRATIONDATE else ?? end

谢谢!!

Answer 1

这些要求对我来说似乎很可疑，但这是我对此的看法(不需要“条件逻辑”)以及为什么它看起来“可疑”，如果我正确地阅读它的话..

Records should stay on the report If RecordStatus = 'Funded', has no PURCHASEDATE, and if the Record has expired (is in the past from currentdate).

RecordStatus = 'Funded'
AND PurchaseDate IS NULL
AND ExpiredDate < CURRENT_TIMESTAMP

Records should fall off report If the Record has expired AND if RecordStatus != 'Funded'.

NOT (HasExpired AND RecordStatus <> 'Funded')

综合:

RecordStatus = 'Funded'
AND PurchaseDate IS NULL
AND ExpiredDate < CURRENT_TIMESTAMP
AND NOT (HasExpired AND RecordStatus <> 'Funded')

随着德摩根的转型:

RecordStatus = 'Funded'
AND PurchaseDate IS NULL
AND ExpiredDate < CURRENT_TIMESTAMP
AND (NOT HasExpired OR RecordStatus = 'Funded')

分发后消除包含条件。

RecordStatus = 'Funded'
AND PurchaseDate IS NULL
AND ExpiredDate < CURRENT_TIMESTAMP
AND NOT HasExpired

但是，假设HasExpired与 ExpiredDate < CURRENT_TIMESTAMP 相同

RecordStatus = 'Funded'
AND PurchaseDate IS NULL
AND ExpiredDate < CURRENT_TIMESTAMP
AND NOT ExpiredDate < CURRENT_TIMESTAMP

这当然不是真的，为什么我会受到怀疑。