mysql - 查询结果中缺少今天的日期

Question

我有一个场景，从表中获取一些数据并按日期顺序显示它。它必须仅显示 10 条记录，即……今天日期之前 4 天和今天日期之后 5 天。下面的查询一切正常，但我的结果中缺少当前日期

select fr.startDate,sum(fr.completed_visit) as completed_visit,sum(fr.canceled_visit) as canceled_visit,sum(fr.Failed_visit) as Failed_visit,    sum(fr.buyer_visit) as buyer_visit,sum(fr.buyerre_visit) as buyerre_visit,sum(fr.visit_sheduled) as visit_sheduled, sum(fr.visit_conferm) as visit_conferm from (select  start_date as startDate,timecreated ,DATE_FORMAT(timecreated,'%d-%b-%Y') as timeformat,
case when (visit_status='Completed') then 1 else 0 end completed_visit,   
case when (visit_status='Cancelled') then 1 else 0 end canceled_visit,
case when (visit_status='Cancelled/Failed') then 1 else 0 end    Failed_visit,
case when (visit_purpose='BuyerVisit') then 1 else 0 end buyer_visit,
case when (visit_purpose='BuyerReVisit') then 1 else 0 end buyerre_visit,
case when (confirm_status='0') then 1 else 0 end visit_sheduled,
case when (confirm_status='1') then 1 else 0 end visit_conferm 
from mg_visit_listing  order by startDate desc) fr
where  fr.startDate <=CurrentDate+5 group by fr.startDate order by fr.startDate desc  ;

我没有获取大于 CurrentDate+5 的值，但结果中缺少我的当前日期

Answer 1

您没有定义 CurrentDate，或者是您之前定义的变量？

比较日期时，您应该使用 date_add和 date_diff

此外，更详细的解释也没有什么坏处(例如创建表和一些开始插入)。