在mysql中获取过去6个月的记录

3z6pesqy  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(595)

我一直在处理一个返回过去6个月的查询,例如:
如果当前月份是2018年4月,则查询应返回最近6个月的记录,例如:
2017年11月
2017年12月
2018年1月
2018年2月
2018年3月
2018年4月
我当前的查询是:

SELECT DISTINCT
  monthname(batchEndDate) AS month,
  batchDetails.bATCHiD,
  batchDetails.batchEndDate,
  COUNT(EnrollmentNumber) AS count
FROM candidate
INNER JOIN batchdetails
  ON candidate.batchId = batchDetails.batchId
WHERE (assessmentResult = "pass"
OR assessmentResult = "fail")
AND YEAR(batchEndDate) BETWEEN YEAR(batchEndDate) - 1
AND YEAR(curdate())
GROUP BY MONTH(batchEndDate)
ORDER BY month(batchEndDate);
vfh0ocws

vfh0ocws1#

这是mysql非常有用的日期算法的工作。特别是 LAST_DAY() 函数使这变得简单。
下个月的第一天可以通过以下方式检索:

LAST_DAY(CURDATE()) + INTERVAL 1 DAY

所以,你想要所有的记录。。。。

WHERE batchEndDate < LAST_DAY(CURDATE()) + INTERVAL 1 DAY

六个月前一个月的第一天是

LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 6 MONTH

所以,你想要那之后的所有记录。

WHERE batchEndDate < LAST_DAY(CURDATE()) + INTERVAL 1 DAY
    AND batchEndDate >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 6 MONTH

你想按月分组。你和我一起做

GROUP BY LAST_DAY(batchEndDate)

那么您的查询如下所示

select LAST_DAY(batchEndDate) as month_ending,
       monthname(LAST_DAY(batchEndDate)) as month,
       count(EnrollmentNumber) as count 
  from candidate 
 inner join batchdetails on candidate.batchId = batchDetails.batchId 
 where (assessmentResult="pass" or assessmentResult="fail") 
   and batchEndDate < LAST_DAY(CURDATE()) + INTERVAL 1 DAY
   and batchEndDate >= LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 6 MONTH
 GROUP BY LAST_DAY(batchEndDate)

相关问题