MySQL 返回应该为 Null 的值

Question

我正在编写一个查询，该查询将查看上周并选择一个值并给出它已使用的计数...非常简单的概念，除了当我运行查询时我得到一个结果而系统没有’上周已使用!

SELECT re.topic, COUNT(*) AS TimesUsed 
FROM ost_ticket AS us
  JOIN ost_help_topic AS re ON re.topic_id = us.topic_id
WHERE re.created  >= CURDATE() - INTERVAL (WEEKDAY(CURDATE())+7) DAY
  AND re.created  <  CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY
GROUP BY us.topic_id  ORDER BY TimesUsed DESC LIMIT 1

我可以确认上周没有任何票证，因为当我运行查询以查看上周是否创建了任何内容时，答案为空。

SELECT number
FROM ost_ticket
WHERE created  >= CURDATE() - INTERVAL (WEEKDAY(CURDATE())+7) DAY
  AND created  <  CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY; 

所以我的问题本质上是我在获取上周日期的日期选择上做错了什么？

Answer 1

您正在混合间隔和日期值。如果您想检查系统上周是否在使用，只需从 curdate() 中减去 7 天的间隔即可:

SELECT re.topic, COUNT(*) AS TimesUsed 
FROM ost_ticket AS us
  JOIN ost_help_topic AS re ON re.topic_id = us.topic_id
WHERE re.created  >= CURDATE() - INTERVAL 7 DAY -- Here!
  AND re.created  <= CURDATE() -- And here
GROUP BY us.topic_id  ORDER BY TimesUsed DESC LIMIT 1