MySQL查询与计数和分组

本文介绍了MySQL查询与计数和分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个表格，其中包含发布商的不同记录，每个记录都有一个类型为时间戳记的日期。

I've got table a table with different records for publishers, each record have a date in a column of type timestamp.

id | id_publisher | date 1 1 11/2012 03:09:40 p.m. 2 1 12/2012 03:09:40 p.m. 3 2 01/2013 03:09:40 p.m. 4 3 01/2013 03:09:40 p.m. 5 4 11/2012 03:09:40 p.m. 6 4 02/2013 03:09:40 p.m. 7 4 02/2012 03:09:40 p.m.

我需要计算每个发布商每个月发布的记录数。例如

I need a count for number of records published by each publisher for each month. For example

Month | id_publisher | num 11/2012 | 1 | 1 11/2012 | 2 | 0 11/2012 | 3 | 0 11/2012 | 4 | 1 ..... 02/2013 | 4 | 2

我尝试使用从raw_occurrence_record组中按月（日期）选择计数，id_publisher;

I tried with select count(id) from raw_occurrence_record group by month(date), id_publisher;

但不起作用。

推荐答案

您的日期是实际的 datetime 列：

SELECT MONTH(date), YEAR(date), id_publisher, COUNT(*) FROM raw_occurrence_record GROUP BY MONTH(date), YEAR(date), id_publisher

您可以连接您的月份&年如此：

You can concatenate your month & year like so:

SELECT CONCAT(MONTH(date), '/', YEAR(date)) AS Month, id_publisher, COUNT(*) FROM raw_occurrence_record GROUP BY MONTH(date), YEAR(date), id_publisher

b $ b

要查找没有记录的月份，您需要一个日期表。如果您无法创建一个，您可以 UNION ALL 这样的日历表：

SELECT a.year, a.month, b.id_publisher, COUNT(b.id_publisher) AS num FROM (SELECT 11 AS month, 2012 AS year UNION ALL SELECT 12, 2012 UNION ALL SELECT 1, 2013 UNION ALL SELECT 2, 2013) a LEFT JOIN raw_occurence_record b ON YEAR(b.date) = a.year AND MONTH(b.date) = a.month GROUP BY a.year, a.month, b.id_publisher