我有一种情况,我想按日期选择记录,但每个记录的"记录选择"日期都不同,因为我需要向其中添加days_gap
。
所以我需要这样的东西:
`select from user_options where email_sent_at < time_now - days_gap`
有可能吗?
DB结构:user_options
user_id | email_sent_at | days_gap
1 | 2016-07-27 | 2
2 | 2016-07-24 | 2
3 | 2016-07-22 | 5
4 | 2016-07-21 | 3
要从日期减去天数,可以使用MySQL的INTERVAL,它接受一列作为天数的参数。
create table user_options
( user_id int auto_increment primary key,
email_sent_at date not null,
days_gap int not null
);
insert user_options values
(1, '2016-07-27', 2),
(2, '2016-07-24', 2),
(3, '2016-07-22', 5),
(4, '2016-07-21', 3);
select * from user_options where email_sent_at < CURRENT_DATE - INTERVAL days_gap DAY;
+---------+---------------+----------+
| user_id | email_sent_at | days_gap |
+---------+---------------+----------+
| 2 | 2016-07-24 | 2 |
| 4 | 2016-07-21 | 3 |
+---------+---------------+----------+