带有计数和分组依据的 MySQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14344810/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Query with count and group by
提问by user1783933
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
I tried with
select count(id) from raw_occurrence_record group by month(date), id_publisher;
我试过
select count(id) from raw_occurrence_record group by month(date), id_publisher;
but, it did not work.
但是，它没有用。
回答by Kermit
Assuming that your date is an actual datetimecolumn:
假设您的日期是一个实际的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
To find months where there are no records, you will need a date table. If you can't create one, you can UNION ALLa calendar table like so:
要查找没有记录的月份，您需要一个日期表。如果你不能创建一个，你可以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
