按年份进行group,统计排序:select yera(established_time),count(*) as num from `table_record` group by year (established_time)
按月份进行group,统计排序:方法一:简单实用
select year(established_time) as 'year' , month(established_time) as 'month' , count(*) as 'count' from `table_record` group by year (established_time) desc, month(established_time)
效果:
year month count
------ ------ --------
2014 1 8320
2014 2 5837
2014 3 25069
2014 4 29910
2014 5 25018
2014 6 17347
2014 7 1
2013 1 9114
2013 2 4258
方法二:利用mysql内置字符串连接函数 concat(str1, str2, ...,str3) 。
selectconcat(year(established_time),',',month( established_time)) as data_time ,count(*) as num from `table_record` #group by data_timegroup by year(established_time) desc,month( established_time)
效果:
data_time num
--------- --------
2014,1 8320
2014,2 5837
2014,3 25069
2014,4 29910
2014,5 25018
2014,6 17347
2014,7 1
2013,1 9114
2013,2 4258
注意这里不要用:
selectyear(established_time)+month( established_time) as data_time
这是错误的,它会造成2010+1=2011的这样的错误。
此外,若仅仅用下面语句,是统计的是多年来每月的数据。
selectmonth(established_time) as 'month' , count(*) as 'count' from `tb_gongshangju_record_beijing` group bymonth(established_time) desc
效果如下:
month count
------ --------
12 44952
11 49720
10 38587
9 48967
8 52874
7 54082
6 69532
5 76999
4 87289
3 85249
2 39997
1 49017
按日期day进行group,统计排序select year(established_time) as 'year' , month(established_time) as 'month' ,day(established_time) as 'day', count(*) as 'count' from `table_record` where table_record.`established_time` >= '2014-01-01'group by year (established_time) desc, month(established_time) desc ,day(established_time) desc