mysql - 在mysql中获取最近6个月的记录

Question

我一直在研究返回最近 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);

Answer 1

这是MySQL非常有用的日期算法的工作。特别是 LAST_DAY() function使这变得简单。

可以通过以下方式检索下个月的第一天:

 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)