MySQL查询与计数和分组 [英] MySQL Query with count and group by
本文介绍了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
要查找没有记录的月份,您需要一个日期表。如果您无法创建一个,您可以<$ p $ c> 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
这篇关于MySQL查询与计数和分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文