MySQL持续7天被忽略

Question

我需要得到所有的付费和临时入境从过去7天，但我不断得到所有的回报。我不知道我做错了什么，我已经阅读了相当多的帖子在这里，无法弄清楚它是什么。MySQL 5.6，如果它与我所做的有任何不同。

SELECT
DATE_FORMAT(FROM_UNIXTIME(ct.entry_date),'%d/%m/%Y') AS booking_date,
cd.field_id_69 AS marriage_date,
cd.field_id_54 AS email_address,
CONCAT(cd.field_id_9, ' ', cd.field_id_10) AS bride_name,
CONCAT(cd.field_id_13, ' ', cd.field_id_14) AS groom_name,
ctco.title AS centre_and_course_date,
(SELECT DATE_FORMAT(FROM_UNIXTIME(col_id_1),'%d/%m/%Y') FROM 
exp_channel_grid_field_50 cg WHERE cg.entry_id = ctco.entry_id ORDER BY 
cg.row_id DESC LIMIT 1) AS course_end_date,
ct.status AS payment_status
FROM exp_channel_titles ct
JOIN exp_channel_data cd ON cd.entry_id = ct.entry_id
JOIN exp_relationships rco ON rco.parent_id = ct.entry_id AND rco.field_id = 41
JOIN exp_channel_titles ctco ON rco.child_id = ctco.entry_id
WHERE ct.channel_id = 2
AND ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND ct.status = 'Paid'
AND ct.status = 'Provisional';

Answer 1

您的WHERE语句有问题

  AND ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND ct.status = 'Paid' AND ct.status = 'Provisional';

中间关键字需要后面跟着2个DATE，因此ct.status = 'Paid‘将转换为返回NULL的日期。因此，您的WHERE语句将变成。

  AND ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND NULL AND ct.status = 'Provisional';

结果是，查询将返回状态=“临时”的所有数据。

您可以尝试将WHERE语句修改为

  AND ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() AND ct.status IN ('Paid', 'Provisional');

Answer 2

BETWEEN的语法是BETWEEN startdate AND enddate。你写道：

ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND ct.status = 'Paid'

所以它使用DATE_SUB(CURDATE(), INTERVAL 7 DAY)作为开始日期，ct.status = 'Paid'作为结束日期。我很惊讶它能匹配任何东西。

改为：

ct.entry_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()

或者，如果你将来没有任何日期，你可以简单地写：

ct.entry_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)