使用count和group by的MySQL查询
使用count和group by的MySQL查询
提问于 2013-01-16 02:52:30
我已经为发布者创建了一个表，表中有不同的记录，每个记录在类型为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.我需要每个出版商每个月发布的记录数量的计数。例如
Month | id_publisher | num
11/2012 | 1 | 1
11/2012 | 2 | 0
11/2012 | 3 | 0
11/2012 | 4 | 1
.....
02/2013 | 4 | 2我试着用select count(id) from raw_occurrence_record group by month(date), id_publisher;
但是，它并没有起作用。
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-01-16 02:56:32
假设您的日期是一个实际的datetime列：
SELECT MONTH(date), YEAR(date), id_publisher, COUNT(*)
FROM raw_occurrence_record
GROUP BY MONTH(date), YEAR(date), id_publisher你可以像这样连接你的月份和年份：
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一个日程表：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14344810
复制相关文章
相似问题