Mysql is sorted by month and by day by group.

In databases, we often use SQL statements to perform simple statistical sorting on tables. For date fields, we can simply order them. For complex operations, you can perform group and order by year, month, and day of the date.

Group by year, statistical sorting:

SELECT YERA(established_time)  ,COUNT(*) AS num <span style="font-size: 13.75px; font-family: Arial, Helvetica, sans-serif;">FROM `table_record` GROUP BY YEAR (established_time)</span>

Group by month, statistical sorting:

Method 1: simple and practical

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)

Effect:

Year month count
-------------------
2014 1 8320
2014 2 5837
2014 3 25069
2014 4 29820
2014 5 25060
2014 6 17615
2014 7 1
2013 1 9114
2013 2 4258

Method 2: Use the MySql built-in string connection function CONCAT (str1, str2,..., str3 ).

SELECT  CONCAT(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)

Effect:

Data_time num
-----------------
8320
5837
Copyright 3 25069
Copyright 4 29910
Copyright 5 25018
17347
Copyright 7 1
9114
4258
2011,3 8047

Note:

SELECT  YEAR(established_time)+MONTH( established_time) as data_time 

This is incorrect. It may cause 2010 + 1 = 2011 errors.

In addition, if you only use the following statement, the monthly data is collected over the years.

SELECT  MONTH(established_time) AS 'month' , COUNT(*) AS 'count' FROM `tb_gongshangju_record_beijing` GROUP BY  MONTH(established_time) DESC

The effect is as follows:

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
(NULL) 34456

Group by date day, statistical sorting
 

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