带有计数和分组依据的 MySQL 查询

2021-12-30 00:00:00 group-by count 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 group by month(date), id_publisher中选择count(id);

但是，它没有用.

推荐答案

假设您的日期是实际的 datetime 列:

Assuming that your date is an actual datetime column:

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

要查找没有记录的月份，您需要一个日期表.如果你不能创建一个，你可以像这样UNION ALL一个日历表:

To find months where there are no records, you will need a date table. If you can't create one, you can UNION ALL a calendar table like so:

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

相关文章