Mysql：每月计数记录（包括零） [英] Mysql: Count records (including zero) per month

问题描述

我正在计算表格中的记录，并按照日期对它们进行分组。我目前的查询看起来如下所示：

  SELECT $ b $ count（*），
 MONTH（time ）as month，
 YEAR（time）as year 
 FROM 
 myTable 
 GROUP BY 
月，年
 ORDER BY 
年，月
  

这个工作原理，除了我还想在没有记录的月份得到一个计数。

任何人都可以提供关于如何完成此操作的建议/建议吗？

解决方案

<在MySQL中最简单的方法是创建一个名为 months 的表格，列出您感兴趣的所有月份并在表格中使用LEFT JOIN。

$ $ p $ SELECT
年（时间）AS年
月（时间）AS月，
COUNT myTable.year）AS cnt，
FROM months
LEFT JOIN myTable
ON months.year = myTable.year
AND months.month = myTable.month
GROUP BY months.year，months.month
ORDER BY months.year，months.month


然而，因为这主要是一个演示问题，所以通常只需运行查询就可以了，因为您已经在做这些操作并将结果转换为客户端（例如PHP）。

I am trying to count the records in my table and group them per date. My current query looks something like the following:

SELECT
   count(*), 
   MONTH(time) as month,
   YEAR(time) as year
FROM
   myTable
GROUP BY
   month, year
ORDER BY
   year, month

This works, except that I would also like to get a count for months where no records exists.

Could anyone offer advice/suggestions on how to accomplish this?

解决方案

The simplest way to do this in MySQL is to create a table called months that lists all the months you are interested in and use a LEFT JOIN to your table.

SELECT
   YEAR(time) AS year
   MONTH(time) AS month,
   COUNT(myTable.year) AS cnt, 
FROM months
LEFT JOIN myTable 
    ON months.year = myTable.year
    AND months.month = myTable.month
GROUP BY months.year, months.month
ORDER BY months.year, months.month

However since this is mostly a presentation issue it is often easier just run the query as you are already doing and transform the result in the client (e.g. PHP).

这篇关于Mysql：每月计数记录（包括零）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！