mysql date_sub 使用字段作为间隔

Question

我需要有关 mysql 和 date_sub() 的帮助。我有一个表调用事件

Activity(id,deadline,alert)
Activity(1,'2011-04-18','1 DAY');
Activity(2,'2011-04-13','1 MONTH');

A 中的每一行都有一个“警报”，该字段指示事件必须在截止日期之前多长时间报告。

例如

On 2011-04-17 I have to report the activity with 'id' 1On 2011-03-14 I have to report the activity with 'id' 2

I trying to use date_sub() functions, but I can't use a field as params of this function. Any idea how to fix this?

SELECT * 
  FROM `activities` 
 WHERE date_sub(`deadline`, INTERVAL alert) >= CURDATE();

Answer 1

将警报拆分为 2 个字段

Alert_count: integer
Alert_period: enum('hour','day','month','week')

然后像这样更改查询:

SELECT * 
  FROM `activities` 
 WHERE CASE alert_period 
   WHEN 'hour' THEN date_sub(`deadline`, INTERVAL alert_count HOUR) >= CURDATE();
   WHEN 'day' THEN date_sub(`deadline`, INTERVAL alert_count DAY) >= CURDATE();
   ...
 END CASE