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

BETWEEN 关键字后面需要跟 2 个日期，因此 ct.status = 'Paid' 将转换为 DATE，并返回 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');