mysql date_sub 使用字段作为间隔 [英] mysql date_sub using a field as interval

问题描述

我需要有关 mysql 和 date_sub() 的帮助.我有一个表调用 Activity

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

A 中的每一行都有一个警报"，该字段表示活动必须在截止日期前多长时间报告.

例如

<前>在 2011-04-17 我必须报告带有id"1 的活动在 2011-03-14 我必须报告带有id"2 的活动

我尝试使用 date_sub() 函数，但我不能使用字段作为此函数的参数.知道如何解决这个问题吗?

SELECT *从`活动`WHERE date_sub(`deadline`, INTERVAL alert) >= CURDATE();

解决方案

将警报分成 2 个字段

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

并像这样更改查询:

SELECT *从`活动`WHERE CASE alert_periodWHEN 'hour' THEN date_sub(`deadline`, INTERVAL alert_count HOUR) >= CURDATE();WHEN 'day' THEN date_sub(`deadline`, INTERVAL alert_count DAY) >= CURDATE();...结案

I need help with mysql and date_sub(). I have a table call Activity

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

Every row in A have an 'alert', this field indicate how time before the deadline an activity have to reported.

For example

On 2011-04-17 I have to report the activity with 'id' 1
On 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();

解决方案

Split the alert into 2 fields

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

And change the query like so:

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

这篇关于mysql date_sub 使用字段作为间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！