我有一个快速和迅速的问题。
SELECT `deals`.*
FROM `deals`
WHERE
`is_featured` = 1 AND
`status` = 'active' AND
CURDATE() BETWEEN start_date AND DATE_ADD(end_date, INTERVAL 1 DAY)
ORDER BY `end_date` DESC
LIMIT 1是这样的吗?
start_date为"2012-01-11 00:00:00“,end_date为"2012-01-11 23:59:59”。
这就是我想要展示的:
我想要显示当前datetime之间的交易优惠,如果is_featured为1,status为"active“。
(CURDATE()只给我日期,不给时间,这是问题所在吗?如何在MySql中获取当前日期时间?)
它应该只挑选一笔交易,并且此交易应该是离当前日期时间最近的交易结束日期。
发布于 2012-01-11 07:39:12
这是您的原始查询
SELECT `deals`.* FROM `deals`
WHERE `is_featured` = 1
AND `status` = 'active'
AND CURDATE() BETWEEN start_date AND DATE_ADD(end_date, INTERVAL 1 DAY)
ORDER BY `end_date` DESC
LIMIT 1;将中间替换为以下内容
SELECT `deals`.* FROM `deals`
WHERE `is_featured` = 1
AND `status` = 'active'
AND start_date >= (CURDATE() + INTERVAL 0 SECOND)
AND start_date <= (end_date + INTERVAL 1 DAY)
ORDER BY `end_date` DESC
LIMIT 1;并添加此索引
ALTER TABLE deals ADD INDEX (is_featured,status,start_date,end_date);发布于 2012-01-11 07:43:42
使用now()获取日期和时间。
发布于 2012-01-11 07:42:19
您可以使用curtime()函数来检索当前时间。
https://stackoverflow.com/questions/8812158
复制相似问题