在數(shù)據(jù)庫中我們經(jīng)經(jīng)常使用sql語句對表進(jìn)行簡單的統(tǒng)計(jì)排序，對于日期字段。我們能夠簡單的對其進(jìn)行order。
對于復(fù)雜一點(diǎn)的能夠按日期中的年。月，日分別進(jìn)行g(shù)roup，order。
按年份進(jìn)行g(shù)roup，統(tǒng)計(jì)排序：
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>按月份進(jìn)行g(shù)roup。統(tǒng)計(jì)排序：
方法一：簡單有用
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 29820
2014 5 25060
2014 6 17615
2014 7 1
2013 1 9114
2013 2 4258
方法二：利用 MySql內(nèi)置字符串連接函數(shù)
CONCAT(str1, str2, ...,str3) 。
SELECT CONCAT(YEAR(established_time),',',MONTH( established_time)) AS data_time ,COUNT(*) AS numFROM `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
2013,3 8047
注意這里不要用：
SELECT YEAR(established_time)+MONTH( established_time) as data_time 這是錯(cuò)誤的，它會造成2010+1=2011的這種錯(cuò)誤。
此外，若只用以下語句，是統(tǒng)計(jì)的是多年來每月的數(shù)據(jù)。
SELECT MONTH(established_time) AS 'month' , COUNT(*) AS 'count'FROM `tb_gongshangju_record_beijing` GROUP BY MONTH(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
(NULL) 34456
按日期day進(jìn)行g(shù)roup，統(tǒng)計(jì)排序
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
