你可以结合 CASE
与 DATE_SUB
得到的过期日期在一个子查询。 然后,它很容易进行比较和分析每一种情况。
例如:
select *,
case when expiring_date < curdate() then 'Expired'
when expiring_date > curdate() then 'Active'
else 'Expires Today'
end as status
from (
select *,
case when sub_type = 'week' then date_add(last_renewal, interval sub_duration week)
when sub_type = 'month' then date_add(last_renewal, interval sub_duration month)
when sub_type = 'year' then date_add(last_renewal, interval sub_duration year)
end as expiring_date
from subscription
) x
结果是:
id sub_type sub_duration last_renewal expiring_date status
--- --------- ------------- ------------- -------------- -------
1 month 2 2021-04-12 2021-06-12 Expired
2 week 1 2021-07-11 2021-07-18 Expired
3 week 4 2021-11-11 2021-12-09 Active
看运行的如在 DB小提琴.