php - 介于两者之间的选择不适用于 MySQL？

Question

我有一个快速且尽快的问题。

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”。

这就是我想展示的:

我想显示当前日期时间和 is_featured 为 1 且 status 为“事件”之间的交易。

(CURDATE() 只给我日期，而不是时间，这是问题所在吗？如何在 MySql 中获取当前日期时间？)。

它应该只选择一笔交易，并且该交易应该是结束日期距离当前日期时间最近的一笔交易。

Answer 1

这是您的原始查询

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;

用这个替换 BETWEEN

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);