집 >데이터 베이스 >MySQL 튜토리얼 >Mysql 按月份为组进行统计排序_MySQL
在数据库中我们经常用sql语句对表进行简单的统计排序,对于日期字段,我们可以简单的对其进行order。对于复杂一点的可以按日期中的年,月,日分别进行group,order。
SELECT YERA(established_time),COUNT(*) AS num FROM `table_record` GROUP BY YEAR (established_time)
方法一:简单实用
SELECT YEAR(established_time) AS 'year' , MONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM <span style="font-family: Arial, Helvetica, sans-serif;">`table_record` </span><pre name="code" class="sql"><span style="font-family: Arial, Helvetica, sans-serif;">GROUP BY YEAR (established_time) DESC, MONTH(established_time)</span>
效果:
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
注意这里不要用:
<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">SELECTYEAR(established_time)+MONTH( established_time) as data_time </span>
这是错误的,它会造成2010+1=2011的这样的错误。
此外,若仅仅用下面语句,是统计的是多年来每月的数据。
<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">SELECTMONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM `tb_gongshangju_record_beijing` GROUP BYMONTH(established_time) DESC</span>
效果如下:
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
<span style="font-family:Microsoft YaHei UI, Microsoft YaHei, SimSun, Segoe UI, Tahoma, Helvetica, sans-serif, Microsoft YaHei, Georgia, Helvetica, Arial, sans-serif, 宋体, PMingLiU, serif;">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</span>